Re: Changing number format

well, add a 'DECODE' func to your list, and you can do your conditional logiv within a SQL statement.. If length(bla) do this, else do that.

On Mon, 14 Aug 2000 19:21:06 -0700, gdas <gdas1NOgdSPAM_at_yahoo.com.invalid> wrote:

>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 - 22:19:14 CDT