Ad

Formatting Phone Numbers In Non-US Format

- 1 answer

I have the following code that I'm trying to refactor to format a phone number field in the format I need it in:

STUFF(STUFF(STUFF(REPLACE('02 212345678','02 2','02 '), 7, 0, ' '), 3, 0, ') '), 1, 0, '(') 

It returns data currently as this:

(02)  123 45678

where I need it in this format

(02) 1234 5678

The problem is the extra space after the closing bracket and having 4 numbers either side.

Ad

Answer

Based on your example, does the following work for you?

with sampledata as (select '02 212345678' num)

select Concat(Stuff('() ',2,0,Left(num,2)), Stuff(Right(num,8),5,0,' '))
from sampledata
Ad
source: stackoverflow.com
Ad