...

View Full Version : DB Query - result not getting



sreejithpm
05-09-2011, 06:22 PM
Hi All,

I am facing a query problem. Record is existing there in DB, but while querying the result is not getting. Query is:

SELECT * FROM tbl_names WHERE LCASE(Lastname) like "%mathew\'s%";

Please check the query and let me know if the query have any problem.

Advance thanks...

Fumigator
05-09-2011, 09:34 PM
What is the value of that column in your table? Does it actually contain that escape character?

Old Pedant
05-09-2011, 10:25 PM
Why not first try it WITHOUT the \' ???

SELECT * FROM tbl_names WHERE LCASE(Lastname) like "%mathew%";

Also, unless you are using an unsual field type for Lastname you don't need to use LCASE( ).

SQL is normally *NOT* case sensitive.

So try

SELECT * FROM tbl_names WHERE Lastname like "%mathew%";

Finally, what kind of database is this? If it's not MYSQL, then \' will never work.

Old Pedant
05-09-2011, 10:32 PM
Oh, and are you sure the name is "mathew"??? Usually, that name is spelled with two T's: "Matthew".

sreejithpm
05-11-2011, 01:21 PM
Why not first try it WITHOUT the \' ???

SELECT * FROM tbl_names WHERE LCASE(Lastname) like "%mathew%";

Also, unless you are using an unsual field type for Lastname you don't need to use LCASE( ).

SQL is normally *NOT* case sensitive.

So try

SELECT * FROM tbl_names WHERE Lastname like "%mathew%";

Finally, what kind of database is this? If it's not MYSQL, then \' will never work.
Hello,

Actually the table contains that value [table value is Mathew\'s].
Mysql is the database. I have used LCASE to include both cases records in to the result set.

Please let me know whats the actual problem...

Fumigator
05-11-2011, 07:15 PM
The problem is the backslash needs to be escaped with another backslash. But, when using LIKE pattern matching, you have to use four backslashes in a row (\\\\) to represent a single backslash. According to the MySQL manual this is because the parser strips the escape character once (leaving \\), and then once again when the pattern match is made (leaving a single \).

So... try this. (I used double single quotes to escape the single quote so the 4 backslashes don't get all muddled with a backslash to escape the quote)



SELECT * from table1 where column1 LIKE '%Mathew\\\\''s%'

Old Pedant
05-11-2011, 09:49 PM
This is only true if using PHP or JSP to create the string for the query.

PHP and Java both "eat" one level of doubled \

And then, as FouLu said, MySQL eats one more level.

I use MySQL from ASP, where the language doesn't "eat" a level. (Well, it would if I used C#, but even in C# there is a way to ask it to NOT eat a level.) So I tend to forget about the problem.

Fou-Lu
05-11-2011, 09:53 PM
And this is why I love prepared statements ^.^
Well, once the GPC is gone that is ;)

sreejithpm
05-12-2011, 07:39 AM
The problem is the backslash needs to be escaped with another backslash. But, when using LIKE pattern matching, you have to use four backslashes in a row (\\\\) to represent a single backslash. According to the MySQL manual this is because the parser strips the escape character once (leaving \\), and then once again when the pattern match is made (leaving a single \).

So... try this. (I used double single quotes to escape the single quote so the 4 backslashes don't get all muddled with a backslash to escape the quote)



SELECT * from table1 where column1 LIKE '%Mathew\\\\''s%'

Hi,

Its working fine now...

Thanks a lot Fumigator...



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum