Page 1 of 1

special selection

Posted: Thu May 20, 2004 4:59 pm
by James M.
Hi, i am trying to extract data from a MySQL database where i only extract the data from a row that begins with a certain letter of the alphabet, like in table 'users' row 'user' i am trying to select all the users who have names beginning with the letter 'A'. Does anyone know a way?

Posted: Thu May 20, 2004 5:01 pm
by feyd
SELECT u.user FROM users u WHERE u.user LIKE 'a%'

I think...

Posted: Thu May 20, 2004 5:01 pm
by tim
read on the LIKE command

combine that with the wildcard % and you have a search function for MySQL

Posted: Thu May 20, 2004 5:02 pm
by tim
Feyd ya got me again.

:oops: :cry: :oops:

Posted: Thu May 20, 2004 5:13 pm
by James M.
feyd wrote:SELECT u.user FROM users u WHERE u.user LIKE 'a%'

I think...
Yup thats it, thanks.

But one more thing, how would u filter it so that only numbers/special characters are selected?

Posted: Thu May 20, 2004 5:22 pm
by feyd
regex!

http://dev.mysql.com/doc/mysql/en/Regexp.html

SELECT u.user FROM users u WHERE u.user REGEXP '^a[^[:alnum:]]'

might need to be:
SELECT u.user FROM users u WHERE u.user = REGEXP '^a[^[:alnum:]]'

I, uh, think..

Posted: Thu May 20, 2004 5:28 pm
by James M.
Thanks, i didn't know regular expressions could be used in MySQL, i've gotta read that manual....thanks for the help once again.

Posted: Thu May 20, 2004 7:26 pm
by James M.
im having trouble with REGEXP, im tryin to search for any users that do not begin with an alpha character.

i tried this but its not working:
SELECT u.user FROM users u WHERE u.user = REGEXP '^[^[:alpha:]]'

Posted: Thu May 20, 2004 8:02 pm
by feyd
tried running that in phpmyadmin?

I just checked in mine..

try:
SELECT u.user FROM users u WHERE u.user REGEXP '^[^[:alpha:]]'

that worked for me.

Posted: Thu May 20, 2004 8:43 pm
by James M.
I got it to work in my script now. I had to find something to initiate the query. Thanks a lot for the help.