Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New Coder
    Join Date
    Feb 2008
    Location
    @ God's Own Country
    Posts
    57
    Thanks
    3
    Thanked 5 Times in 5 Posts

    Question DB Query - result not getting

    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...
    ---
    By
    Sreejith.P.M

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    What is the value of that column in your table? Does it actually contain that escape character?

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Oh, and are you sure the name is "mathew"??? Usually, that name is spelled with two T's: "Matthew".
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New Coder
    Join Date
    Feb 2008
    Location
    @ God's Own Country
    Posts
    57
    Thanks
    3
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by Old Pedant View Post
    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...
    ---
    By
    Sreejith.P.M

  • #6
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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)

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

  • Users who have thanked Fumigator for this post:

    sreejithpm (05-12-2011)

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    And this is why I love prepared statements ^.^
    Well, once the GPC is gone that is

  • #9
    New Coder
    Join Date
    Feb 2008
    Location
    @ God's Own Country
    Posts
    57
    Thanks
    3
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by Fumigator View Post
    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)

    Code:
    SELECT * from table1 where column1 LIKE '%Mathew\\\\''s%'
    Hi,

    Its working fine now...

    Thanks a lot Fumigator...


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •