Saturday 8 November 2014

Phone numbers adding 000

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

No comments:

Post a Comment