Select non-alphabetic characters only

[shadowcaster]

How do I select non-alphabetic characters from a field in my table? I don't want any characters that are a to z, just other symbols and numbers. If this is not possible, can I just select numbers?

Thanks.

[wickning1]

I'm not exactly sure what you're asking.. are letters mixed with symbols and numbers in the same row and column, or is this a column where each row is either alphabetic or not alphabetic?

[fenway]

Not that I know what you're after, but REGEXP can take character classes -- e.g. [:alpha:] and [:digit:] -- that should be useful.

[shadowcaster]

Hello again. Sorry I wasn't clear before.
I am trying to write a php script that searches the names of singers and categorizes them by the first letter of the name. E.g. when I click [a] (hyperlink) I want the page to show me all the singers whose name begins with a.
But there are also people who don't have an alphabetic character for the first letter in their name (or their songs). How do I search for them?

So far I have this:
[code]
SELECT name FROM singers WHERE name REGEXP '[^a-zA-Z]' ORDER BY name; //selects non-alphabetic singer's names
//&
SELECT name FROM singers WHERE name REGEXP '^$letter' ORDER BY name; //$letter could be any alphabetic character.[/code]
Should I just use SELECT name FROM singers WHERE name LIKE '$letter%' ORDER BY name; instead for the second one?

Am I doing this right?

[fenway]

I see no reason not to use LIKE if you're only looking for the first character, provided you have an index on this column.

[shadowcaster]

Thanks guys...

So I ended up using this: SELECT name FROM singers WHERE name REGEXP '[^a-zA-Z]' ORDER BY name; //selects non-alphabetic singer's names

and it worked but not how I wanted. It selected the songs that started with brackets () and numbers but it also selected a few song-names that begin with A too! However, only a few A songs were selected from all the A songs. I think I can stick with it but I would prefer it if there was a work-around/fix.

[fenway]

It doesn't look like you're anchoring your REGEXP to the beginning of the string.

[wickning1]

[code]SELECT name FROM singers WHERE name REGEXP '^[^a-zA-Z]' ORDER BY name[/code]
Here's how you'd anchor it to the beginning. That will return anything that starts with a non-alphabetic character.

[shadowcaster]

Thanks guys. I really appreciate you guys helping me, where would we database newbies be without you?!

Just one last question about another issue, is there any point in putting A-Z (in capitals) since mysql seems to select enteries when they don't exactly have the same case as in the database; If I was searching for a word and forgot to use strtolower() to make a name like Elvis to lowercase and compared it to the database that stores all the names in lowercase, then it would still return the Elvis record. In the database it would be 'elvis' but I just searched for 'Elvis' and it still returned with that record. What do you think? is there any point to using A-Z in the regexp? (is there a speed issue involved?)

[wickning1]

Case sensitivity depends upon the collation of the text you're comparing. Some collations are case sensitive, others are not. That regex will work no matter what, whereas depending on case insensitivity could be a bad decision. There will not be a significant performance gain in either case.