Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

MySQL upper/lowercase search problem

Discussion in 'General Discussion' started by cbwass, Feb 1, 2006.

  1. cbwass

    cbwass Well-Known Member

    Joined:
    Mar 29, 2002
    Messages:
    148
    Likes Received:
    0
    Trophy Points:
    316
    After upgrading to MySQL 4.1 from 4.0 I have a customer that when he makes a search it returns different results depending if the word starts with Upper or Lower case. (He would like a search for John or john to return the same results).

    Does anyone have an idea on how to fix this?


    Example:

    SELECT id FROM latin_news where id>'0' AND sp_text LIKE '%john%' (returns 0 results)

    SELECT id FROM latin_news where id>'0' AND sp_text LIKE '%John%' (returns 3 results)
     
  2. webignition

    webignition Well-Known Member

    Joined:
    Jan 22, 2005
    Messages:
    1,880
    Likes Received:
    0
    Trophy Points:
    166
    You could make use of the LOWER function.

    Code:
    SELECT id FROM latin_news where id>'0' AND LOWER(sp_text) LIKE LOWER('%John%')
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. cbwass

    cbwass Well-Known Member

    Joined:
    Mar 29, 2002
    Messages:
    148
    Likes Received:
    0
    Trophy Points:
    316
    webignition thanks,
    I tried that but get 0 results with both upper and lower.
     
  4. webignition

    webignition Well-Known Member

    Joined:
    Jan 22, 2005
    Messages:
    1,880
    Likes Received:
    0
    Trophy Points:
    166
    Well, I might have got the query syntax a slight bit off, but the principle still stands - the UPPER and LOWER functions respectively return uppercase and lowercase strings.

    Perhaps

    Code:
    SELECT id FROM latin_news where id>'0' AND LOWER(sp_text) LIKE '%'.LOWER('John').'%'
    would work better.

    Best try things out with some test data and try having a look at the online MySQL manual.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  5. cbwass

    cbwass Well-Known Member

    Joined:
    Mar 29, 2002
    Messages:
    148
    Likes Received:
    0
    Trophy Points:
    316
    Thanks 'webignition ' will keep searching for the correct syntax.
    If anyone has a sugestion please post.
     
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice