CODEWITHSUNDEEP
sql-servert-sql
Philip
Philip

Reputation: 2628

Formatting Phone Numbers in Non-US format

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.

Upvotes: 0

Views: 99

Answers (2)

Nick Fotopoulos
Nick Fotopoulos

Reputation: 579

You existing code works, just need to change the 7 to an 8, but I far prefer to keep my formatting and data seperated. The code is waaaaaay easier to read and modify:

DECLARE @PhoneNumber varchar(20) = '02 212345678';

SELECT FORMATMESSAGE('(%s) %s %s', LEFT(@PhoneNumber, 2), SUBSTRING(@PhoneNumber, 4, 4), SUBSTRING(@PhoneNumber, 8, 4) );

Upvotes: 0

Stu
Stu

Reputation: 32599

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

Upvotes: 1

Related Questions