CREATE TABLE TBL_PHON_NUMBS(ID INT IDENTITY,PH_NUM BIGINT)
INSERT INTO TBL_PHON_NUMBS VALUES(1234567890),(123456789),(987456123),(65498730)
SELECT * FROM TBL_PHON_NUMBS
SELECT CASE WHEN LEN(CAST(PH_NUM AS VARCHAR(MAX)))=10 THEN
LEFT(CAST(PH_NUM AS VARCHAR(MAX)),3)+'-'+
LEFT(RIGHT(CAST(PH_NUM AS VARCHAR(MAX)),7),3)
+'-'+RIGHT(CAST(PH_NUM AS VARCHAR(MAX)),4)
WHEN LEN(CAST(PH_NUM AS VARCHAR(MAX)))=9 THEN
+'0'+LEFT(CAST(PH_NUM AS VARCHAR(MAX)),2)+'-'+
LEFT(RIGHT(CAST(PH_NUM AS VARCHAR(MAX)),7),3)
+'-'+RIGHT(CAST(PH_NUM AS VARCHAR(MAX)),4)
WHEN LEN(CAST(PH_NUM AS VARCHAR(MAX)))=8 THEN
+'00'+LEFT(CAST(PH_NUM AS VARCHAR(MAX)),1)+'-'+
LEFT(RIGHT(CAST(PH_NUM AS VARCHAR(MAX)),7),3)
+'-'+RIGHT(CAST(PH_NUM AS VARCHAR(MAX)),4)
ELSE CAST(PH_NUM AS VARCHAR(MAX)) END FROM TBL_PHON_NUMBS
INSERT INTO TBL_PHON_NUMBS VALUES(1234567890),(123456789),(987456123),(65498730)
SELECT * FROM TBL_PHON_NUMBS
SELECT CASE WHEN LEN(CAST(PH_NUM AS VARCHAR(MAX)))=10 THEN
LEFT(CAST(PH_NUM AS VARCHAR(MAX)),3)+'-'+
LEFT(RIGHT(CAST(PH_NUM AS VARCHAR(MAX)),7),3)
+'-'+RIGHT(CAST(PH_NUM AS VARCHAR(MAX)),4)
WHEN LEN(CAST(PH_NUM AS VARCHAR(MAX)))=9 THEN
+'0'+LEFT(CAST(PH_NUM AS VARCHAR(MAX)),2)+'-'+
LEFT(RIGHT(CAST(PH_NUM AS VARCHAR(MAX)),7),3)
+'-'+RIGHT(CAST(PH_NUM AS VARCHAR(MAX)),4)
WHEN LEN(CAST(PH_NUM AS VARCHAR(MAX)))=8 THEN
+'00'+LEFT(CAST(PH_NUM AS VARCHAR(MAX)),1)+'-'+
LEFT(RIGHT(CAST(PH_NUM AS VARCHAR(MAX)),7),3)
+'-'+RIGHT(CAST(PH_NUM AS VARCHAR(MAX)),4)
ELSE CAST(PH_NUM AS VARCHAR(MAX)) END FROM TBL_PHON_NUMBS
No comments:
Post a Comment