From: Peter Brawley
Date: November 17 2005 6:51pm
Subject: Re: A bit of SQL help for a MySQL novice.
List-Archive: http://lists.mysql.com/mysql/191795
Message-Id: <437CD117.2010605@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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