Professional Web Applications Themes

where concat is case sensitive!? - MySQL

Hello. I have this query SELECT CONCAT(PMEjoin1.name,PMEjoin1.id) AS qf1 FROM kontakte AS PMEtable0 LEFT OUTER JOIN annadev.firmen AS PMEjoin1 ON (PMEjoin1.id = PMEtable0.firma_id) WHERE CONCAT(PMEjoin1.name,PMEjoin1.id) LIKE '%abc%' My problem is that the result depends on the case sensitiv LIKE statement (abc != ABC). But this problem is only if I use CONCAT in WHERE clause. Does somebody have a solution? Karsten...

  1. #1

    Default where concat is case sensitive!?

    Hello.

    I have this query

    SELECT CONCAT(PMEjoin1.name,PMEjoin1.id) AS qf1
    FROM kontakte AS PMEtable0
    LEFT OUTER JOIN annadev.firmen AS PMEjoin1 ON (PMEjoin1.id =
    PMEtable0.firma_id)
    WHERE CONCAT(PMEjoin1.name,PMEjoin1.id) LIKE '%abc%'

    My problem is that the result depends on the case sensitiv LIKE
    statement (abc != ABC). But this problem is only if I use CONCAT in
    WHERE clause.

    Does somebody have a solution?

    Karsten

    Karsten Guest

  2. #2

    Default Re: where concat is case sensitive!?

    postscript
    id = int(10)
    name = varchar(30)

    Karsten

    Karsten Guest

  3. #3

    Default Re: where concat is case sensitive!?

    Karsten wrote:
     

    SELECT CONCAT(PMEjoin1.name,PMEjoin1.id) AS qf1
    FROM kontakte AS PMEtable0
    LEFT OUTER JOIN annadev.firmen AS PMEjoin1 ON (PMEjoin1.id =
    PMEtable0.firma_id)
    WHERE to_lower(CONCAT(PMEjoin1.name,PMEjoin1.id)) LIKE '%abc%'


    --
    Michael Austin.
    Database Consultant
    Michael Guest

  4. #4

    Default Re: where concat is case sensitive!?

    Hi Michael,

    in the column are upper and lower characters. If I search for abc I get
    abc, if I search for ABC I get ABC. But my users want to type lower
    characters to find both upper and lower like all other sql where
    statements.

    Thanks
    Karsten

    Karsten Guest

  5. #5

    Default Re: where concat is case sensitive!?

    Karsten wrote:
     

    Karsten - a bit of advice. You could look this up very easily on the MYSQL
    website.

    http://dev.mysql.com/doc/refman/5.1/en/string-functions.html

    or in the search bar, enter whatever you are trying to achieve.

    lcase() is a synonym for lower()
    or you could use upper().

    Either way, you are trying to ensure that the search is case-blind.

    mysql> select * from a;
    +------+
    | a |
    +------+
    | ABC |
    | abc |
    +------+
    2 rows in set (0.00 sec)

    mysql> select a from a where lcase(a) = lcase('ABC');
    +------+
    | a |
    +------+
    | ABC |
    | abc |
    +------+
    2 rows in set (0.01 sec)

    mysql> select a from a where upper(a) = upper('ABC');
    +------+
    | a |
    +------+
    | ABC |
    | abc |
    +------+
    2 rows in set (0.00 sec)

    mysql> select a from a where lower(a) = lower('ABC');
    +------+
    | a |
    +------+
    | ABC |
    | abc |
    +------+
    2 rows in set (0.01 sec)

    --
    Michael Austin.
    Database Consultant.
    Michael Guest

Similar Threads

  1. SQL 92 - case sensitive
    By rabaaoui abdelhak in forum MySQL
    Replies: 1
    Last Post: February 28th, 05:49 PM
  2. #23026 [Com]: Make Zend case-sensitive (classes, functions, remove case-insensitive)
    By nvivo at mandic dot com dot br in forum PHP Development
    Replies: 0
    Last Post: October 19th, 12:17 PM
  3. Case Sensitive
    By amit in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 31st, 12:32 PM
  4. Glob() is Case Sensitive
    By Krhis in forum PHP Development
    Replies: 1
    Last Post: July 26th, 04:30 AM
  5. getPos is case sensitive
    By Lingo Dude in forum Macromedia Director Lingo
    Replies: 9
    Last Post: July 17th, 05:29 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139