Re: Changing number format

anurag <aminochaNOamSPAM_at_unibiz.com.invalid> wrote:
>HI,
>
>I have a column with phone numbers inserted. It is a varchar
>column and the numbers are inserted in the form 3013971234.
>
>I need to update the column so that the numbers should now
>appear as 301-397-1234.
>Also some numbers might have the area code and some might not
>example 9996576 and they need to be updated to 999-6576.
>
>I am unable to write an update statement to do this.
>
>Any help will be appreciated.
>

Personally, I would use PL/SQL for this.

In PL/SQL, I would first use the length sql function to determine if there was an area code or not (length = 7 = no area code). Then I would use the substring function (substr) to generate the new value:

if phone_length = 9 then
var := substr(phone,1, 3)||'-'||substr(phone,4,4) else
var := substr(phone,1, 3)||'-'||substr(phone,4,3)||'-'substr (phone,8,4);
end if;
(I'm simply concatenating the "-" with the substrings of the various parts of the phone number....)

You should be able to do this in straight sql, however, because you mention that some numbers do and don't have area codes, you need conditional / procedural logic on this one...Hence PL/SQL.

>Thanks
>Anurag
>
>
>-----------------------------------------------------------
>
>Got questions? Get answers over the phone at Keen.com.
>Up to 100 minutes free!
>http://www.keen.com
>
>

Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free!
http://www.keen.com Received on Mon Aug 14 2000 - 21:21:06 CDT