special selection

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
James M.
Forum Contributor
Posts: 119
Joined: Wed Mar 31, 2004 6:41 pm
Location: Tallahassee

special selection

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

SELECT u.user FROM users u WHERE u.user LIKE 'a%'

I think...
User avatar
tim
DevNet Resident
Posts: 1165
Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio

Post by tim »

read on the LIKE command

combine that with the wildcard % and you have a search function for MySQL
User avatar
tim
DevNet Resident
Posts: 1165
Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio

Post by tim »

Feyd ya got me again.

:oops: :cry: :oops:
User avatar
James M.
Forum Contributor
Posts: 119
Joined: Wed Mar 31, 2004 6:41 pm
Location: Tallahassee

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
User avatar
James M.
Forum Contributor
Posts: 119
Joined: Wed Mar 31, 2004 6:41 pm
Location: Tallahassee

Post 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.
User avatar
James M.
Forum Contributor
Posts: 119
Joined: Wed Mar 31, 2004 6:41 pm
Location: Tallahassee

Post 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:]]'
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
James M.
Forum Contributor
Posts: 119
Joined: Wed Mar 31, 2004 6:41 pm
Location: Tallahassee

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