Jump to content

Select non-alphabetic characters only


shadowcaster

Recommended Posts

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?
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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?)
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.