The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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:
    16
    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:
    36
    You could make use of the LOWER function.

    Code:
    SELECT id FROM latin_news where id>'0' AND LOWER(sp_text) LIKE LOWER('%John%')
     
  3. cbwass

    cbwass Well-Known Member

    Joined:
    Mar 29, 2002
    Messages:
    148
    Likes Received:
    0
    Trophy Points:
    16
    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:
    36
    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.
     
  5. cbwass

    cbwass Well-Known Member

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

Share This Page