CODEWITHSUNDEEP
sqlsql-servert-sql
chaitu chaitanya reddy
chaitu chaitanya reddy

Reputation: 17

How to split the numeric values string split into digits in SQL Server

ID  PHONE
1   9701245929
2   66663333
3   9701245931
4   9701245932
5   26668888
6   48228899
7   88229933

OUTPUT:

ID  PHONE
1   9701 245 929
2   6666 3333
3   9701 245 931
4   9701 245 932
5   2666 8888
6   4822 8899
7   8822 9933

Upvotes: 0

Views: 2941

Answers (3)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You need format():

select format(PHONE, '### ### ###') as Phone
from table t;

Edit : You can use case expression for conditional formatting 

select *, (case when len(Phone) = 8
                 then format(Phone, '#### ####')
                 else format(Phone, '#### ### ###')
            end) as Phone
from table t;

Upvotes: 0

SQLChao
SQLChao

Reputation: 7847

You could use case and build the string or format as others have suggested.

SELECT
  id
 ,CASE 
    WHEN LEN(phone) = 10 THEN SUBSTRING(phone, 1, 4) + ' ' + SUBSTRING(phone, 5, 3) + ' ' + SUBSTRING(phone, 8, 3)
    WHEN LEN(phone) = 8 THEN LEFT(phone, 4) + ' ' + RIGHT(phone, 4)
  END
FROM YourTable

Upvotes: 1

DhruvJoshi
DhruvJoshi

Reputation: 17126

You can use a query like below See working demo

select id,
phone=case 
    when 
        len(phone)=10 
    then
        FORMAT(cast(phone as bigint), '### ### ###')  
     when 
        len(phone)=8 
    then
        FORMAT(cast(phone as bigint), '#### ####') 
end
from t;

Upvotes: 2

Related Questions