Filtering is not working as expected

Question

3.50/5 (2 votes)

See more:

I have a table tbltest and 3 column(id,subject,status).While implementing search using the "IN"
keyword in sql as follows:-

SQL

select * from tbltest where Rtrim(Ltrim(Subject)) in ('Call')

then it shows nothing and i have a value 'Call' in column Subject

but when i use

SQL

select * from tbltest where Rtrim(Ltrim(Status)) in ('Completed')

then it works fine.
I had tried to remove the leading and trailing spaces using rtrim and ltrim but wasn't successfull.What could be the problem in it.

Posted 10-Jan-14 2:28am

Sumit Bhargav

Updated 10-Jan-14 4:28am

Mike Meinz

v2

Add a Solution

2 solutions

Solution 1

SQL

select * from tbltest where Rtrim(Ltrim(Subject)) in ('Call')

select * from tbltest where Rtrim(Ltrim(Status)) in ('Completed')

In the fist statement you have use Subject column but in second statement you have used Status.
Was it intentional?

Posted 10-Jan-14 2:38am

Deviprasad Das

Comments

Sumit Bhargav 10-Jan-14 8:43am

yes,please read the question carefully

Deviprasad Das 10-Jan-14 8:47am

Try using

select * from tbltest where replace(Subject, ' ', '') in ('Call')

Sumit Bhargav 10-Jan-14 8:49am

hi deviprasad,
that dosen't work either!

Deviprasad Das 10-Jan-14 9:09am

Check this
http://www.sqlfiddle.com/#!2/dd4919/4

Karthik_Mahalingam 11-Jan-14 7:24am

5,
your solution is correct only..
It was a data issue...

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Karthik_Mahalingam · Accepted Answer · 2014-01-10T02:42:00

Solution 3

SQL

select * from tbltest where Rtrim(Ltrim(Subject)) in ('Call')

in the above query you are using the column Subject whereas in the below you are using the column Status

SQL

select * from tbltest where Rtrim(Ltrim(Status)) in ('Completed')

Please make sure your column names are correct ??

Posted 10-Jan-14 2:42am

Karthik_Mahalingam

Comments

Sumit Bhargav 10-Jan-14 8:46am

hi karthik,
the second query is an example to explain which is executing successfully.
so why isn't the first query running successfully?

Karthik_Mahalingam 10-Jan-14 8:51am

:) OMG, sorry i misunderstood.

Karthik_Mahalingam 10-Jan-14 8:51am

did u tried with Mike Meinz 's solution ??

Sumit Bhargav 10-Jan-14 8:54am

i can't use mike menz's solution coz it's dynamically binded and many other values can come more

Karthik_Mahalingam 10-Jan-14 8:58am

can u pls post the subject column values..

Karthik_Mahalingam 10-Jan-14 9:03am

run this query and check what you are getting..

select Rtrim(Ltrim(Subject)) from tbltest

Sumit Bhargav 11-Jan-14 0:40am

i get the results as

ghhg

Email

Meeting

Call

sad

sdsa

xz

Email

Sumit

gfdgf

dgfdgfg

Send Letter

sfsdfsdfsdf

Send Letter

Bhargav

jhgjgdfgfdsdg

new trace

ffffff

dfgfddfgdf

pipipio

asdsfdsf

Sumit

Email

but why isn't it showing anything when i m using it with "IN" keyword?

Karthik_Mahalingam 11-Jan-14 4:34am

which version of sql u r using?

Sumit Bhargav 11-Jan-14 5:07am

i am using sql server 2012

Karthik_Mahalingam 11-Jan-14 5:12am

do u have team viewer ?

Sumit Bhargav 11-Jan-14 5:14am

yes
i have it version 8

Karthik_Mahalingam 11-Jan-14 5:28am

ok can u share the id
i wil guide you

Sumit Bhargav 11-Jan-14 5:42am

id is 112 008 413
pasword is 8250

Mike Meinz 10-Jan-14 10:27am

KARTHIK Bangalore is on the right track. Your data is suspect. Please post results of
select Rtrim(Ltrim(Subject)) from tbltest
in your question.
Use the Improve Question link.

Are you showing us the actual SQL SELECT statement that you are using to test?

Sumit Bhargav 11-Jan-14 0:42am

yes i am showing the actual select statement just the table name is different

Mike Meinz 11-Jan-14 7:18am

How was this problem solved?

Karthik_Mahalingam 11-Jan-14 7:21am

hi MIke,
i had took remote desktop on his machine and checked the data.
it was a data issue
the "Call" value is saved in the table as [(enter key value) + "Call"]

Mike Meinz 11-Jan-14 9:51am

Thanks, I figured it must be in the data. So, the lesson learned is if it is not the code, it is probably the data.

Karthik_Mahalingam 11-Jan-14 10:16am

yes Mike :)

Sumit Bhargav 13-Jan-14 0:41am

Thanks all of you for your precious help.