when is select records no record found

Question

i have table like this below.....

sno name mobile
1 raju 9xx393xxxx
2 vamshi 9856784562

iam using below quarie

select * from tablename where mobile='9856784562'

its shows no records found...

but iam using below quarie is shows the record.........

select * from tablename where mobile like '%9856784562%'

it's working ..................

what is the wrong on first quarie ...

plz tell what is the error ..................

Jignesh Trivedi · Answer

Hi Just want to know whether this data issue or any other issue... I think your mobile file containing tab space or new line space that will not remove by Ltrim and rtrim use following query to update table records update tablename set mobile= LTRIM(RTRIM(REPLACE(REPLACE(mobile char(9) '') char(13) ''))) hope this will easy to update....

Vulpes · Answer

Just edit your post(s) to change that number to something else.

Sanjay Dholakiya · Answer

Hi there... What is the mobile column datatype? - Sanjay

JAYRAM · Answer

ur code is working ........ v v thanks Jignesh Trivedi ........

Vulpes · Answer

One thing that's just dawned on me is that you're using unicode (i.e. NVARCHAR) rather than ascii strings for the mobile column. See if this query will work: select * from tablename where mobile = N' 9856784562'

Vulpes · Answer

Does the query fail just on this mobile number or on others as well? If it doesn't seem to be an isolated problem I'd try and find the cause before you update anything.

JAYRAM · Answer

its ok above i given ex only ...... but when i compare mobile field to another table mobile field same mobile no's but not matching ... i have more then 2000 record ...... using ur quries more time is takes... update 2000 record ???????

Jignesh Trivedi · Answer

do one thing update this filed using SQL query update tablename set mobile='9856784562' where sno = 2 now run the query select * from tablename where mobile='9856784562' is it give you correct result?

Vulpes · Answer

The only other thing I can think of is that the database value contains some other invisible character (such as TAB) as I'm not sure that LTRIM and RTRIM removes anything other than blanks. You could try: select * from tablename where replace(columnname char(9) '')='9856784562' If it still doesn't work then we need to analyse it to see what it does contain. If you retrieve the string from the database using the LIKE query then run this C# code on it to see what it contains: string contents = ; foreach(char c in mobile) contents += String.Format({0:X2}  c); Response.Write(contents);

JAYRAM · Answer

plz tell me ...how to remove whitespaces..

JAYRAM · Answer

datatype is navrchar(max)

Jignesh Trivedi · Answer

hi What is data type of your mobile filed? it might be possible your mobile filed is char and may this containing blank space.... so it is working with like % but not working with =.

JAYRAM · Answer

again showing sama error ...................

Vulpes · Answer

Try: select * from tablename where ltrim(rtrim(mobile))='9856784562'

JAYRAM · Answer

Thanks for replay ........... iam using below quarie to remove whitespaces SELECT LTRIM ( RTRIM ( YourColumn )) FROM YourTable again showing same error ... how to remove white spaces plz help me..........

Vulpes · Answer

The most likely explanation is that the value for that field in the database contains some leading or trailing white-space. The second query will then find it but the first one won't.