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
-
You want to split it into certain groups of numbers? Your numbers are split differently into different groups, how are you determining which one to split twice only and which one to split 3 times? By the length of the full string? What have you tried so far?Brad– Brad2018-06-06 14:47:56 +00:00Commented Jun 6, 2018 at 14:47
-
if phone number is 10 digits it will split into every 3 digits with space and if it is 8 digits split into every 4 digits with space.chaitu chaitanya reddy– chaitu chaitanya reddy2018-06-06 14:48:54 +00:00Commented Jun 6, 2018 at 14:48
-
What have you tried? What research have you done? In what manner of writing does "9701" look like three digits?HABO– HABO2018-06-06 17:46:57 +00:00Commented Jun 6, 2018 at 17:46
Add a comment
|
3 Answers
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;
Comments
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
Comments
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;
