Sidebar Sidebar
Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

macOS like [charlist] not working on MySQL (phpMyAdmin)

Darkroom

Darkroom

Guest
Original poster
Dec 15, 2006
2,445
0
Montréal, Canada
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].
 
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.
 
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
 
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%'
 
Darkroom said:
all suggestions return zero results as well...

if i put double quotes around "Persons", i actually get a syntax error... strange...
Click to expand...

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
 
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]%'...
 

Attachments

  • Picture 5.png
    Picture 5.png
    92.4 KB · Views: 623
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?
 
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
 
lee1210 said:
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
Click to expand...

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.
 
Cromulent said:
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?
Click to expand...

The page I linked above states:

A character class “[...]” matches any character within the brackets. For example, “[abc]” matches “a”, “b”, or “c”. To name a range of characters, use a dash. “[a-z]” matches any letter, whereas “[0-9]” matches any digit.
Click to expand...

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

-Lee
 
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.
 
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
 
lee1210 said:
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
Click to expand...

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.
 
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
 
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

Darkroom said:
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.
Click to expand...

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
 
You must log in or register to reply here.
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.
Top Bottom
Back