Changing Phone Numbers

0

Please login or register to vote for this query.

(click on this box to dismiss)

Science Fiction and Fantasy

Q&A for science fiction and fantasy enthusiasts

DECLARE @phoneNumbers TABLE(
  oldNumber varchar(20)
);

INSERT INTO @phoneNumbers(oldNumber) VALUES('0102547894')
INSERT INTO @phoneNumbers(oldNumber) VALUES('0162554852')
INSERT INTO @phoneNumbers(oldNumber) VALUES('0192025514')
INSERT INTO @phoneNumbers(oldNumber) VALUES('0151257894')
INSERT INTO @phoneNumbers(oldNumber) VALUES('01515474894')

INSERT INTO @phoneNumbers(oldNumber) VALUES('0122547894')
INSERT INTO @phoneNumbers(oldNumber) VALUES('0182547894')
INSERT INTO @phoneNumbers(oldNumber) VALUES('0172547894')
INSERT INTO @phoneNumbers(oldNumber) VALUES('0150547894')
INSERT INTO @phoneNumbers(oldNumber) VALUES('0101054789')
INSERT INTO @phoneNumbers(oldNumber) VALUES('01501547894')

INSERT INTO @phoneNumbers(oldNumber) VALUES('0112547894')
INSERT INTO @phoneNumbers(oldNumber) VALUES('0142547894')
INSERT INTO @phoneNumbers(oldNumber) VALUES('0152047894')
INSERT INTO @phoneNumbers(oldNumber) VALUES('01529656894')
INSERT INTO @phoneNumbers(oldNumber) VALUES('02187878748')
INSERT INTO @phoneNumbers(oldNumber) VALUES('0221558488')
INSERT INTO @phoneNumbers(oldNumber) VALUES('0228488')

SELECT * FROM @phoneNumbers;

UPDATE @phoneNumbers 
SET oldNumber = CASE  
                           WHEN LEN(oldNumber) = 10 AND SUBSTRING(oldNumber, 1, 3) = '010'
                             THEN REPLACE(oldNumber, '010', '0100') 
                           WHEN LEN(oldNumber) = 10 AND SUBSTRING(oldNumber, 1, 3) = '016'
                             THEN REPLACE(oldNumber, '016', '0106') 
                           WHEN LEN(oldNumber) = 10 AND SUBSTRING(oldNumber, 1, 3) = '019'
                             THEN REPLACE(oldNumber, '019', '0109')
                           WHEN LEN(oldNumber) = 11 AND SUBSTRING(oldNumber, 1, 4) = '0151'
                             THEN REPLACE(oldNumber, '0151', '0101')
                           WHEN LEN(oldNumber) = 10 AND SUBSTRING(oldNumber, 1, 3) = '012'
                             THEN REPLACE(oldNumber, '012', '0122')
                           WHEN LEN(oldNumber) = 10 AND SUBSTRING(oldNumber, 1, 3) = '018'
                             THEN REPLACE(oldNumber, '018', '0128')
                           WHEN LEN(oldNumber) = 10 AND SUBSTRING(oldNumber, 1, 3) = '017'
                             THEN REPLACE(oldNumber, '017', '0127')
                           WHEN LEN(oldNumber) = 11 AND SUBSTRING(oldNumber, 1, 4) = '0150'
                             THEN REPLACE(oldNumber, '0150', '0120')
                           WHEN LEN(oldNumber) = 10 AND SUBSTRING(oldNumber, 1, 3) = '011'
                             THEN REPLACE(oldNumber, '011', '0111')
                           WHEN LEN(oldNumber) = 10 AND SUBSTRING(oldNumber, 1, 4) = '014'
                             THEN REPLACE(oldNumber, '014', '0114')
                           WHEN LEN(oldNumber) = 11 AND SUBSTRING(oldNumber, 1, 4) = '0152'
                             THEN REPLACE(oldNumber, '0152', '0112')
                     ELSE 'Invalid Number'
                     END ;
                     
 SELECT * FROM @phoneNumbers

/*
SELECT p.oldNumber,  CASE  
                           WHEN LEN(p.oldNumber) = 10 AND SUBSTRING(p.oldNumber, 1, 3) = '010'
                             THEN REPLACE(p.oldNumber, '010', '0100') 
                           WHEN LEN(p.oldNumber) = 10 AND SUBSTRING(p.oldNumber, 1, 3) = '016'
                             THEN REPLACE(p.oldNumber, '016', '0106') 
                           WHEN LEN(p.oldNumber) = 10 AND SUBSTRING(p.oldNumber, 1, 3) = '019'
                             THEN REPLACE(p.oldNumber, '019', '0109')
                           WHEN LEN(p.oldNumber) = 11 AND SUBSTRING(p.oldNumber, 1, 4) = '0151'
                             THEN REPLACE(p.oldNumber, '0151', '0101')
                           WHEN LEN(p.oldNumber) = 10 AND SUBSTRING(p.oldNumber, 1, 3) = '012'
                             THEN REPLACE(p.oldNumber, '012', '0122')
                           WHEN LEN(p.oldNumber) = 10 AND SUBSTRING(p.oldNumber, 1, 3) = '018'
                             THEN REPLACE(p.oldNumber, '018', '0128')
                           WHEN LEN(p.oldNumber) = 10 AND SUBSTRING(p.oldNumber, 1, 3) = '017'
                             THEN REPLACE(p.oldNumber, '017', '0127')
                           WHEN LEN(p.oldNumber) = 11 AND SUBSTRING(p.oldNumber, 1, 4) = '0150'
                             THEN REPLACE(p.oldNumber, '0150', '0120')
                           WHEN LEN(p.oldNumber) = 10 AND SUBSTRING(p.oldNumber, 1, 3) = '011'
                             THEN REPLACE(p.oldNumber, '011', '0111')
                           WHEN LEN(p.oldNumber) = 10 AND SUBSTRING(p.oldNumber, 1, 4) = '014'
                             THEN REPLACE(p.oldNumber, '014', '0114')
                           WHEN LEN(p.oldNumber) = 11 AND SUBSTRING(p.oldNumber, 1, 4) = '0152'
                             THEN REPLACE(p.oldNumber, '0152', '0112')
                     ELSE 'Invalid Number'
                     END AS 'New Number'
FROM @phoneNumbers p
*/

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results