Results 1 to 3 of 3

Thread: why is this case sensitive?

  1. #1
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    2,260
    Thanks
    96
    Thanked 104 Times in 102 Posts

    Default why is this case sensitive?

    Code:
    SELECT ID, ID, summary FROM memoblog WHERE ( concat(ID, summary) LIKE '%wordpress%' )
    will produce 6 results.

    Code:
    SELECT ID, ID, summary FROM memoblog WHERE ( concat(ID, summary) LIKE '%Wordpress%' )
    will produce 2 results.

    How can I get this to be case insensitive? I am using MySQL 5.0 and 4.0.
    Last edited by james438; 12-02-2008 at 03:24 PM.
    To choose the lesser of two evils is still to choose evil. My personal site

  2. #2
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    2,260
    Thanks
    96
    Thanked 104 Times in 102 Posts

    Default

    got it.

    I can make the search terms lowercase. Then I can execute the query like
    Code:
    SELECT ID, ID, summary FROM memoblog WHERE (lcase( concat(ID, summary)) LIKE '%wordpress%' )
    That will look at all of the contents in the database as lowercase as well.

    I am now getting 8 results

    problem solved.
    To choose the lesser of two evils is still to choose evil. My personal site

  3. #3
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    2,260
    Thanks
    96
    Thanked 104 Times in 102 Posts

    Default

    After looking a bit more it seems that the reason has to do with the character set used. some are case sensitive and some are not. The problem is that since I primarily use version 4.0 it is a bit more difficult to check exactly which version is being used.

    SHOW VARIABLES
    SHOW TABLE STATUS
    SHOW STATUS

    all of the above do not give me the information that I need. I have not looked too closely, but MySQL 5.0 appears to have the info available.

    Anyway, using SHOW VARIABLES I was able to discover that I am using character set latin1. Since there are several types of latin my guess is that I am using latin1_general_cs which is the only case sensitive one listed. http://dev.mysql.com/doc/refman/4.1/...set-mysql.html

    This thread was mildly helpful http://forums.mysql.com/read.php?10,...607#msg-204607

    I am not a big fan of the forum that mysql uses. There are far better ones out there that are easier to navigate and faster to use.
    Last edited by james438; 12-11-2008 at 10:44 PM.
    To choose the lesser of two evils is still to choose evil. My personal site

Bookmarks

Posting Permissions

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