Jump to content


Photo

Select non-alphabetic characters only


  • Please log in to reply
9 replies to this topic

#1 shadowcaster

shadowcaster
  • Members
  • PipPip
  • Member
  • 22 posts
  • LocationUK

Posted 28 March 2006 - 09:41 PM

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.
--Lurking out of sight, Night shall fall and feed my frenzy, Beneath an eerie moon a change comes over me--[br]Check out Symphony X (The oddysey album) if you like heavy metal

#2 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 29 March 2006 - 05:19 AM

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?

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 29 March 2006 - 09:37 AM

Not that I know what you're after, but REGEXP can take character classes -- e.g. [:alpha:] and [:digit:] -- that should be useful.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 shadowcaster

shadowcaster
  • Members
  • PipPip
  • Member
  • 22 posts
  • LocationUK

Posted 29 March 2006 - 12:24 PM

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:
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.
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?
--Lurking out of sight, Night shall fall and feed my frenzy, Beneath an eerie moon a change comes over me--[br]Check out Symphony X (The oddysey album) if you like heavy metal

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 29 March 2006 - 07:07 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 shadowcaster

shadowcaster
  • Members
  • PipPip
  • Member
  • 22 posts
  • LocationUK

Posted 31 March 2006 - 10:54 PM

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.
--Lurking out of sight, Night shall fall and feed my frenzy, Beneath an eerie moon a change comes over me--[br]Check out Symphony X (The oddysey album) if you like heavy metal

#7 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 31 March 2006 - 11:45 PM

It doesn't look like you're anchoring your REGEXP to the beginning of the string.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#8 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 01 April 2006 - 01:17 AM

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

#9 shadowcaster

shadowcaster
  • Members
  • PipPip
  • Member
  • 22 posts
  • LocationUK

Posted 02 April 2006 - 10:04 PM

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?)
--Lurking out of sight, Night shall fall and feed my frenzy, Beneath an eerie moon a change comes over me--[br]Check out Symphony X (The oddysey album) if you like heavy metal

#10 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 02 April 2006 - 10:12 PM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users