Please help how to
a) Standardize all phone numbers to the uniform format "89856666677."
b) One client can have multiple phones. Each phone should be on a separate line.
c) The solution should include a script that creates a new (temporary) table to store the results.
d) Select rows with error (impossible to convert to the required format) numbers.
CREATE TABLE #list_phone_client (
clien_id INT,
number VARCHAR(40)
);
INSERT INTO #list_phone_client (clien_id, number) VALUES
You'll need a splitter function; I use dbo.DelimitedSplit8K. Also probably easiest is a function to strip nonnumeric chars from the number, so I created such a function. First, create the two functions -- one time only, won't have to do that again -- then you can run the other code.
CREATE FUNCTION [dbo].[DelimitedSplit8K] (
@pString varchar(8000),
@pDelimiter char(1)
)
RETURNS TABLE WITH SCHEMABINDING
AS
/*SELECT * FROM dbo.DelimitedSplit8K('ab/c/def/ghijklm/no/prq/////st/u//', '/')*/
RETURN
/*Inline CTE-driven "tally table" produces values from 0 up to 10,000: enough to cover varchar(8000).*/
WITH E1(N) AS (SELECT N FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS Ns(N)),
E2(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b), --10E+4 or 10,000 rows max
ctetally(N) AS (/* This provides the "zero base" and limits the number of rows right up front,
for both a performance gain and prevention of accidental "overruns". */
SELECT 0 UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@pString, 1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
), cteStart(N1) AS ( /* This returns N+1 (starting position of each "element" just once for each delimiter). */
SELECT t.N+1
FROM ctetally t
WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)
)
/* Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found. */
SELECT ROW_NUMBER() OVER(ORDER BY s.N1) AS ItemNumber,
SUBSTRING(@pString,s.N1,ISNULL(NULLIF((LEAD(s.N1,1,1) OVER (ORDER BY s.N1) - 1),0) - s.N1,8000)) AS Item
FROM cteStart s;
/*end of func*/
CREATE FUNCTION dbo.remove_nonnumeric_chars
(
@string VARCHAR(200)
)
RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @invalidCharLocation SMALLINT
SET @invalidCharLocation = PATINDEX('%[^0-9]%', @string)
WHILE @invalidCharLocation > 0
BEGIN
SET @string = STUFF(@string, @invalidCharLocation, 1, '')
SET @invalidCharLocation = PATINDEX('%[^0-9]%', @string)
END --WHILE
RETURN @string;
END --FUNCTION
--query to get good phone #s
--INSERT INTO #client_phone_cleaned ( clien_id, number )
SELECT lpc.clien_id, rn.number_cleaned
FROM #list_phone_client lpc
CROSS APPLY dbo.DelimitedSplit8K(number, ',') ds
CROSS APPLY (
SELECT dbo.remove_nonnumeric_chars(ds.Item) AS number_cleaned
) AS rn
WHERE LEN(rn.number_cleaned) = 10 AND rn.number_cleaned LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
--query to get good bad phone #s
SELECT lpc.clien_id, rn.number_cleaned AS bad_number, LEN(rn.number_cleaned) AS bad_number_length
FROM #list_phone_client lpc
CROSS APPLY dbo.DelimitedSplit8K(number, ',') ds
CROSS APPLY (
SELECT dbo.remove_nonnumeric_chars(ds.Item) AS number_cleaned
) AS rn
WHERE LEN(rn.number_cleaned) <> 10 OR rn.number_cleaned NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
