like [charlist] not working on MySQL (phpMyAdmin)

Discussion in 'Mac Programming' started by Darkroom, Nov 24, 2008.

  1. Darkroom Guest

    Darkroom

    Joined:
    Dec 15, 2006
    Location:
    Montréal, Canada
    #1
    i have a table titled "Persons" with several "lastname"(s) beginning with the letter B, but the following query returns no match:

    Code:
    select * from Persons where lastname like '[abc]%'
    
    the table is called "Persons" (with capital P) and "lastname" is the all lowercase column title... writing '[ABC]%' returns zero matches also. does charlist not work with phpMyAdmin? the other SQL wildcards (& and _) work fine, just not [charlist].
     
  2. Cromulent macrumors 603

    Cromulent

    Joined:
    Oct 2, 2006
    Location:
    The Land of Hope and Glory
    #2
    Shouldn't it be:
    Code:
    SELECT * FROM "Persons" WHERE lastname LIKE '[abc]%'
    You need to be careful. You have a capital letter in Persons and I believe that MySQL will treat persons, Persons and PERSONS identically unless you put it in quotes.

    Try it anyway, it might very well be something else.
     
  3. lee1210 macrumors 68040

    lee1210

    Joined:
    Jan 10, 2005
    Location:
    Dallas, TX
    #3
    Don't think in terms of phpMyAdmin, it's just a tool. if it's changing the text of your queries or something, it's not a good one, but hopefully that's not the case. Look into what MySQL supports.

    http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html

    It looks like what you have should be fine for what you are trying to achieve. Have you tried:
    select * from "Persons" where lower(lastname) like 'b%';

    and

    select * from "Persons" where lower(lastname) like '%';

    To see if they work? if not, give them a try and see if things are breaking down elsewhere.

    -Lee
     
  4. Darkroom thread starter Guest

    Darkroom

    Joined:
    Dec 15, 2006
    Location:
    Montréal, Canada
    #4
    all suggestions return zero results as well...

    if i put double quotes around "Persons", i actually get a syntax error... strange...
     
  5. Cabbit macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #5
    i use something like this

    Code:
    SELECT table_als.`field_1`, table_als.`field_2` FROM `table` AS `table_als` WHERE table_als.`field_1` LIKE'%$php_value%'
     
  6. lee1210 macrumors 68040

    lee1210

    Joined:
    Jan 10, 2005
    Location:
    Dallas, TX
    #6
    Right, so do we believe there are items in the table that do start with b?

    Does
    select lastname from persons;

    Return things? Things that appear to start with b or B?

    What about
    select * from persons where lastname like '%';

    What about
    select * from persons where substr(lower(lastname),1,1) = 'b';

    or more generally
    select substr(lower(lastname),1,1) from lastname;

    Do a little more digging and see where things go awry.

    -Lee
     
  7. Darkroom thread starter Guest

    Darkroom

    Joined:
    Dec 15, 2006
    Location:
    Montréal, Canada
    #7
    hi lee,

    it just doesn't seem to work...

    as proof, i've attached a print out of my sql queries and returns. you can see the full 'persons' table in the first print, and the last print you can see that it returns no results for like '[mpl]%'...
     

    Attached Files:

  8. lee1210 macrumors 68040

    lee1210

    Joined:
    Jan 10, 2005
    Location:
    Dallas, TX
    #8
    Try lower(lastname) in that last query, or '[MPL]%' in the predicate.

    -Lee
     
  9. Darkroom thread starter Guest

    Darkroom

    Joined:
    Dec 15, 2006
    Location:
    Montréal, Canada
    #9
    no dice...

    it's so weird! every other basic query from my studies works except [charlist]... i'm sure i could live with that, but i'd just like to know if it's something i'm doing wrong or not...

    could this be just simply not be supported with MyPHPAdmin?
     
  10. lee1210 macrumors 68040

    lee1210

    Joined:
    Jan 10, 2005
    Location:
    Dallas, TX
    #10
    I refuse to believe that this is phpMyAdmin's fault. It seems to be pretty widely used, if it had such a deficiency i can't believe it would not be fixed.

    you tried either:
    select * from persons where lower(lastname) like '[mpl]%';

    or

    select * from persons where lastname like '[MPL]%';

    and got no results?

    -Lee
     
  11. Darkroom thread starter Guest

    Darkroom

    Joined:
    Dec 15, 2006
    Location:
    Montréal, Canada
    #11
    yup, i tried both and both returned zero results...

    it seems to me that when i was tinkering with Sequel Pro last week (before i found MyPHPAdmin) that charlist didn't seem to work either. could there be some setting on my computer that's causing this to happen?

    i would be very interested to know if anyone else using MyPHPAdmin (easily thru XAMPP locally, or on a webserve) is experiencing the same results on charlist queries.
     
  12. Cromulent macrumors 603

    Cromulent

    Joined:
    Oct 2, 2006
    Location:
    The Land of Hope and Glory
    #12
    If there is an error with some programming 99.999999% of the time it is user error.

    What does the MySQL manual say about charlists?
     
  13. lee1210 macrumors 68040

    lee1210

    Joined:
    Jan 10, 2005
    Location:
    Dallas, TX
    #13
    The page I linked above states:

    The page does not, however, contain any example queries that use this, so the syntax can be confirmed. This is pretty perplexing.

    -Lee
     
  14. Cromulent macrumors 603

    Cromulent

    Joined:
    Oct 2, 2006
    Location:
    The Land of Hope and Glory
    #14
    The only problems I can see in the examples above are a missing semi colon at the end of the statement.
     
  15. Darkroom thread starter Guest

    Darkroom

    Joined:
    Dec 15, 2006
    Location:
    Montréal, Canada
    #15
    semicolons don't seem to be required when querying in front ends?

    i realized that i studied [charlist] from an online tutorial on SQL, while in my book about MySQL there is no mention of [charlist], [^charlist] or [!charlist] as examples for wildcards, and i couldn't find anything about these on the mysql site either.

    also, google searching for MySQL wildcards only seem to return examples using "%" and "_".

    so perhaps MySQL just didn't adopt the use of charlist wildcard methods? if that's the case, i'm surprised that phpMyAdmin didn't return a syntax error rather than simply returning zero results.
     
  16. lee1210 macrumors 68040

    lee1210

    Joined:
    Jan 10, 2005
    Location:
    Dallas, TX
    #16
    Try:
    select * from persons where lower(lastname) REGEXP '[mpl].*';

    This is getting into very implementation specific territory. This is not SQL. This is MySQL. I had skimmed the page I linked above, and thought the whole thing was with LIKE, and thought the [] grouping was a MySQL like extension, but it turns out it is part of the REGEXP MySQL extension, instead.

    -Lee
     
  17. Darkroom thread starter Guest

    Darkroom

    Joined:
    Dec 15, 2006
    Location:
    Montréal, Canada
    #17
    regular expressions to the rescue! :p

    that worked... and it seems also not to be case sensitive as removing/including lower(), upper(), lcase() and ucase() functions return the same results.
     
  18. lee1210 macrumors 68040

    lee1210

    Joined:
    Jan 10, 2005
    Location:
    Dallas, TX
    #18
    I'm glad we figured it out. Now you should never use it. If you do, if you switch to another RDBMS, you'll be very sad rewriting all of your queries. I also seriously question the speed of REGEXP on large datasets.

    -Lee
     
  19. mairo macrumors newbie

    Joined:
    Nov 20, 2011
    #19
    thanks

    Thanks guys, I had the same issue, not that i think i will ever have to use that regexp, but still

    and about this

    ti returns zero because it is looking for matches with [abc]something something, and not for the chartlist, for example if you have lastname like [abcolman it would retund that
     
  20. jared_kipe macrumors 68030

    jared_kipe

    Joined:
    Dec 8, 2003
    Location:
    Seattle
    #20

Share This Page