Rick
>I need to read the first 4 positions in the phone number to determine
it's location.
>My statement looks like this:
>'Select mid(phone, 1,4) as phoneareacode from phonetable'
>This works but if the number is entered as 1(203)-555-1212 the above
would return "1(20" which is not >what I am looking for.
You need an unpunct() function. Not available in 4 or 5, easy to write
in 5.0 as a stored function, not hard to add as a 'C' udf in 4.1 if you
write 'C'. Since it's a common requirement, likely someone has written
it. Failing that, you may be stuck with the absurd
replace(replace(replace(replace(replace(@s,'(',''),')',''),'
',''),'-',''),'.','').
PB
-----
Rick Dwyer wrote:
Hello All.
I am hoping for a bit of help with some code that has really given me
some trouble. If this is not he correct forum for this any help in
pointing me to a more suited list would be appreciated.
I have a MySQL 4.1.x database containing records with phone numbers.
Most of the phone numbers are enter in 12035551212 format, but some
are entered with spaces or "-" or "(" or other characters.
I need to read the first 4 positions in the phone number to determine
it's location.
My statement looks like this:
'Select mid(phone, 1,4) as phoneareacode from phonetable'
This works but if the number is entered as 1(203)-555-1212 the above
would return "1(20" which is not what I am looking for.
Is there a way to have the select statement examine only numeric
values in the phone number so it would disregard the other charcters?
In Lasso, you can use a Replace with a Regular Expression function to
have just the digits 0-9 examined but haven't been able find a way
to do this in SQL.
Any help is appreciated.
Thank you.
Rick
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.3/173 - Release Date: 11/16/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]