June 2, 2015 at 2:45 pm
Lowell (6/2/2015)
Eirikur Eiriksson (6/2/2015)
Lowell (6/2/2015)
take a look at this thread from a while ago: it's got some excellent examples on stripping out non-numeric characters in the fastest way possible.I went looking for this but you beat me to it, haven't found anything better than the code from that thread
😎
i only started the ball rolling, you and several others took the performance to new heights on that one. i've got a number of scripts harvested form that awesome thread.
I remember this, was on my then long commute to work and Jeff's comment on scalar while loop function got my attention, just a brilliant example of the SQL Server Community at it;s best.
😎
June 2, 2015 at 2:48 pm
No doubt, it is the best community of SQL 🙂
June 2, 2015 at 3:51 pm
Eirikur Eiriksson (6/2/2015)
Lowell (6/2/2015)
Eirikur Eiriksson (6/2/2015)
Lowell (6/2/2015)
take a look at this thread from a while ago: it's got some excellent examples on stripping out non-numeric characters in the fastest way possible.I went looking for this but you beat me to it, haven't found anything better than the code from that thread
😎
i only started the ball rolling, you and several others took the performance to new heights on that one. i've got a number of scripts harvested form that awesome thread.
I remember this, was on my then long commute to work and Jeff's comment on scalar while loop function got my attention, just a brilliant example of the SQL Server Community at it;s best.
😎
SELECT '+'+DigitsOnly FROM dbo.DigitsOnlyEE('1.000!!!');😎
-- Itzik Ben-Gan 2001
June 2, 2015 at 6:06 pm
ScottPletcher (6/2/2015)
For now you could create a non-persisted computed column you could reference in queries to get the "clean" number. Later you could make it an actual column by doing the initial setting of it for existing rows and allowing a trigger to set the value in the future.First, create the function below, and then add the column to the table like below:
ALTER TABLE table_name
ADD phone_number_clean AS dbo.StripNonnumericChars(phone_number);
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[StripNonnumericChars] (
@string varchar(200)
)
RETURNS varchar(200)
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
DECLARE @byte int
WHILE 1 = 1
BEGIN
SET @byte = PATINDEX('%[^0-9]%', @string)
IF @byte = 0
BREAK
SET @string = STUFF(@string, @byte, 1, '')
END --WHIILE
RETURN @string
END --FUNCTION
"Any reference to a table (or view) with a computed column that uses a T-SQL scalar function will result in a serial plan, even if the problematic column is not referenced in the query."
From Forcing a Parallel Query Execution Plan by SQL MVP Paul White
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 2, 2015 at 7:42 pm
Just another way to approach this... If you know what characters you want to remove you can use this translate function. Note the comments for how to use.
IF OBJECT_ID('dbo.Translate8K') IS NOT NULL DROP FUNCTION dbo.Translate8K;
GO
CREATE FUNCTION dbo.Translate8K
(
@String varchar(8000),
@SearchPattern varchar(100),
@ReplacePattern varchar(100)
)
/*******************************************************************************
Purpose:
function replaces a sequence of characters in a string with another set of
characters one character at a time.
For example, Translate8K will replace the 1st character in @SearchPattern with
the 1st character in @ReplacePattern. Then the 2nd character in @SearchPattern
with the 2nd character in @ReplacePattern, etc.
When @SearchPattern is longer than @ReplacePattern then characters in
@SearchPattern that have no corresponding characters in @ReplacePattern will be
removed. For example, if @SearchPattern = 'A!?' and @ReplacePattern = 'Z' then
all A's will be replaced with Z's and all !'s and ?'s will be removed.
Usage Examples:
-- (1) basic replace characters/remove characters
-- Replace a with A, c with C, b with x and remove $ and #
DECLARE
@String varchar(20)='###$$$aaabbbccc$$$###',
@SearchPattern varchar(5)='acb#$',
@ReplacePattern varchar(5)='ACx';
SELECT
original = @string,
Translated = dbo.Translate8K(@string,@SearchPattern,@ReplacePattern);
GO
-- (2) format phone numbers (using both versions... )
-- (a) format phone (atomic value)
DECLARE
@string varchar(20)='(425)555-1212',
@SearchPattern varchar(5)=')(',
@ReplacePattern varchar(5)='-';
SELECT
original = @string,
Translated = dbo.Translate8K(@string,@SearchPattern,@ReplacePattern);
-- (b) format phone numbers(from table)
WITH phoneNbrs(n,pn) AS
(
SELECT 1, '(425)555-1212' UNION ALL SELECT 2, '425.555.1212' UNION ALL
SELECT 3, '425_555_1212' UNION ALL SELECT 4, '(425)555.1212'
)
SELECT n, pn AS before, [after] = dbo.Translate8K(pn,x,y)
FROM phoneNbrs
CROSS APPLY (VALUES('.)_('+char(32),'---')) t(x,y);
-- (c) hide phone numbers
WITH phoneNbrs(n,pn) AS
(
SELECT 1, '(425) 555-1212' UNION ALL SELECT 2, '425.555.1212' UNION ALL
SELECT 3, '425-555-1212' UNION ALL SELECT 4, '4255551212'
)
SELECT n, pn AS before, [after] = dbo.Translate8K(pn,x,y)
FROM phoneNbrs
CROSS APPLY (VALUES('()-.0123456789','()-.**********')) t(x,y);
GO
Revision History:
Rev 00 - 05/18/2015 Initial Development - Alan Burstein
*******************************************************************************/
RETURNS varchar(8000) WITH SCHEMABINDING AS
BEGIN
WITH
E1(N) AS
(
SELECT 1
FROM (VALUES (null),(null),(null),(null),(null),(null),(null),(null),(null),(null)) AS X(N)
),
iTally(N) AS
(
SELECT TOP (len(@SearchPattern)) (CHECKSUM(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))))
FROM E1 a, E1 b, E1 c
)
SELECT
@string =
replace
(
@string COLLATE Latin1_General_BIN,
substring(@SearchPattern,n,1),
substring(@ReplacePattern,n,1)
)
FROM iTally;
RETURN @string;
END;
GO
For your situation:
DECLARE @PhoneNbrs TABLE (PhoneNbr varchar(20) NOT NULL);
INSERT @PhoneNbrs VALUES
('(555)123-6688'),
('123 777 9999'),
('(576) 555-3333'),
('555-432-1234'),
('5675557777');
SELECT
original = PhoneNbr,
New = dbo.Translate8K(PhoneNbr,' ()-','')
FROM @PhoneNbrs;
-- Itzik Ben-Gan 2001
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply