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
