do you have a StripNonNumeric ITVF function?

+1000 Absolutely Brilliant Sir. I ran accross Adam's make_parallel() some time ago and wish I thought of this.

Well done again Eirikur. I'm going to test this out and update my script. Shortly.

I read through this thread - very interesting...

Got me thinking - why not test this with a CLR.:hehe: I also ran patexclude8k() and patEExclude8k() with make_parallel().

Notes:

1) I am running this on my laptop (8GB ram, 2 cores, 4 logical Windows 8) so my results will be a little slower.

2) I am using the mdq.regexreplace, the regexreplace CLR that ships with MDS/DQS. I'm sure there's faster ones, this one is just the easiest to setup.

3) I tested the CLR against make_parallel() but excluded the results for brevity (they were bad)

Code:

/****** Binary code trunctated. Instructions to get the code can be found here: http://www.sqlservercentral.com/articles/MDS/75932/ ******/

CREATE ASSEMBLY [Microsoft.MasterDataServices.DataQuality]

FROM 0x4D5A90000300000004000000FFF...

WITH PERMISSION_SET = SAFE

GO

CREATE SCHEMA [mdq] AUTHORIZATION [dbo]

GO

/*

mdq.RegexReplace() modified to remove non-numeric characters

notes:

1. nvarchar is required;

2. I changed the @input from nvarchar(4000) to nvarchar(200), @pattern to nvarchar(50), and @REPLACE to nvarchar(1)

for a more "apples-to-apples" comparison

*/

CREATE FUNCTION [mdq].[RegexReplace](@input [nvarchar](200), @pattern [nvarchar](50), @REPLACE [nvarchar](1), @mask [tinyint])

RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER, RETURNS NULL ON NULL INPUT

AS

EXTERNAL NAME [Microsoft.MasterDataServices.DataQuality].[Microsoft.MasterDataServices.DataQuality.SqlClr].[RegexReplace]

GO

PRINT '========== Using PatExclude8K ==========';

DECLARE @String VARCHAR(36)

,@StartTime DATETIME;

SELECT @StartTime = GETDATE();

SELECT @string = newstring

FROM #val

CROSS APPLY dbo.PatExclude8K(txt, '[^0-9]');

PRINT DATEDIFF(ms,@StartTime,GETDATE());

GO 3

PRINT '========== Using PatEExclude8K ==========';

DECLARE @String VARCHAR(36)

,@StartTime DATETIME;

SELECT @StartTime = GETDATE();

SELECT @string = newstring

FROM #val

CROSS APPLY dbo.PatEExclude8K(txt, '[^0-9]');

PRINT DATEDIFF(ms,@StartTime,GETDATE());

GO 3

PRINT '========== mdq.RegexReplace ==========';

DECLARE @String VARCHAR(36)

,@StartTime DATETIME;

SELECT @StartTime = GETDATE();

SELECT @string = mdq.RegexReplace(txt, '[^0-9]', '', 0)

FROM #val

PRINT DATEDIFF(ms,@StartTime,GETDATE());

GO 3

-----------------------------------------------------------

PRINT '========== Using PatExclude8K with make_parallel() ==========';

DECLARE @String VARCHAR(36)

,@StartTime DATETIME;

SELECT @StartTime = GETDATE();

SELECT @string = newstring

FROM #val

CROSS APPLY dbo.PatExclude8K(txt, '[^0-9]')

CROSS APPLY dbo.make_parallel();

PRINT DATEDIFF(ms,@StartTime,GETDATE());

GO 3

PRINT '========== Using PatEExclude8K with make_parallel() ==========';

DECLARE @String VARCHAR(36)

,@StartTime DATETIME;

SELECT @StartTime = GETDATE();

SELECT @string = newstring

FROM #val

CROSS APPLY dbo.PatEExclude8K(txt, '[^0-9]')

CROSS APPLY dbo.make_parallel();

PRINT DATEDIFF(ms,@StartTime,GETDATE());

GO 3

PRINT '========== mdq.RegexReplace with make_parallel() ==========';

DECLARE @String VARCHAR(36)

,@StartTime DATETIME;

SELECT @StartTime = GETDATE();

SELECT @string = mdq.RegexReplace(txt, '[^0-9]', '', 0)

FROM #val

CROSS APPLY dbo.make_parallel();

PRINT DATEDIFF(ms,@StartTime,GETDATE());

GO 3

Results:

Beginning execution loop

========== Using PatExclude8K ==========

2116

========== Using PatExclude8K ==========

2140

========== Using PatExclude8K ==========

2186

Batch execution completed 3 times.

Beginning execution loop

========== Using PatEExclude8K ==========

2083

========== Using PatEExclude8K ==========

2050

========== Using PatEExclude8K ==========

2050

Batch execution completed 3 times.

Beginning execution loop

========== mdq.RegexReplace ==========

1103

========== mdq.RegexReplace ==========

1093

========== mdq.RegexReplace ==========

1136

Batch execution completed 3 times.

Beginning execution loop

========== Using PatExclude8K with make_parallel() ==========

1116

========== Using PatExclude8K with make_parallel() ==========

1153

========== Using PatExclude8K with make_parallel() ==========

1123

Batch execution completed 3 times.

Beginning execution loop

========== Using PatEExclude8K with make_parallel() ==========

1180

========== Using PatEExclude8K with make_parallel() ==========

1106

========== Using PatEExclude8K with make_parallel() ==========

1103

Batch execution completed 3 times.

The CLR was looking like the clear winner until trying magoo's idea to use make_parallel() against the patexclude8K functions.

On that note, one doesn't count the time it takes to make parmegiana reggiano...:-P

😎

Taking this one step further... Why not just replace PatExclude8K with PatReplace8k() ?:w00t:

I had to use logic from earlier in the thread but what about this for starters:

CREATE FUNCTION dbo.PatReplace8K

(

@String VARCHAR(8000),

@Pattern VARCHAR(50),

@Replace VARCHAR(1)

)

RETURNS TABLE WITH SCHEMABINDING

AS

RETURN

WITH E1(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS E1(N)),

Tally(N) AS (SELECT TOP (LEN(@String)) CHECKSUM(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) FROM E1 a,E1 b,E1 c,E1 d)

SELECT NewString =

CAST

(

(

SELECT CASE

WHEN PATINDEX(@Pattern,SUBSTRING(@String COLLATE Latin1_General_BIN,N,1)) = 0

THEN SUBSTRING(@String,N,1)+''

ELSE @replace+''

END

FROM Tally

FOR XML PATH(''), TYPE

)

AS varchar(8000)

);

GO

You still have your patexclude() functionality:

-- replacing patexclude8k with patReplace8k()

-- extract a phone number

SELECT *

FROM dbo.PatReplace8K('Call me at: 555-123-9999','[^0-9-]','')

And you have some new functionality:

-- a T-SQL only alternative to RegexReplace

SELECT *

FROM dbo.PatReplace8K('my credit card number is: 5555-1234-7777-8888','[0-9]','*')

Edit: text and code formatting

Alan.B (11/4/2014)

---

Taking this one step further... Why not just replace PatExclude8K with PatReplace8k() ?:w00t:

I like the idea, tinkered a little bit with the code and got the average execution time on my workbench from 4430 to 3650.

😎

IF OBJECT_ID('dbo.PatREEplace8K') IS NOT NULL DROP FUNCTION dbo.PatREEplace8K;

GO

CREATE FUNCTION dbo.PatREEplace8K

(

@String VARCHAR(8000),

@Pattern VARCHAR(50),

@Replace VARCHAR(1)

)

RETURNS TABLE WITH SCHEMABINDING

AS

RETURN

WITH E1(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS E1(N))

,Tally(N) AS (SELECT TOP (LEN(@String)) CHECKSUM(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) FROM E1 a,E1 b,E1 c,E1 d)

SELECT NewString =

(

(

SELECT STUFF(SUBSTRING(@String,NM.N,1),1,PATINDEX(@Pattern,SUBSTRING(@String COLLATE Latin1_General_BIN,N,1)),@Replace)

FROM Tally NM

FOR XML PATH('')

)

);

GO

Edit: not working

Eirikur Eiriksson (11/6/2014)

---

:ermm:

:blush: just noticed, had several "versions", this one is no good

😎

This guy here is slightly slower than the last (and fastest) version of PatExclude8K that you posted.

ALTER FUNCTION dbo.PatReplace8K

(

@String VARCHAR(8000),

@Pattern VARCHAR(50),

@Replace CHAR(1)

)

RETURNS TABLE WITH SCHEMABINDING

AS

RETURN

WITH E1(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS E1(N)),

Tally(N) AS (SELECT TOP (LEN(@String)) CHECKSUM(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) FROM E1 a,E1 b,E1 c,E1 d)

SELECT NewString =

(

SELECT CASE

WHEN PATINDEX(@Pattern,SUBSTRING(@String COLLATE Latin1_General_BIN,N,1)) = 0

THEN SUBSTRING(@String,N,1)

ELSE

CASE @Replace WHEN '' THEN '' ELSE @Replace END-- this line seems silly but really is required (trust me!)

END +''

FROM Tally

FOR XML PATH('')

);

GO

So here's a question...

Let's say the best we could do is create a PatReplace function that is 10% slower than PatExclude... Would you just use PatReplace and take the minor performance hit so you could have just one function? OR would you create both and recommend that people use PatExclude for stripping characters and patReplace for replacing characters? I am going to add this to my toolbox and was looking for a second opition...

Yes, stay away from the red one:-D

I'll go with both for certain, in fact there are at least five functions coming out of this effort, CleanNum, CleanAlpha, PatInclude, PatExclude and PatReplace.

😎

Contributing a couple of observations:

To guarantee correct concatenation order in the FOR XML PATH, an ORDER BY is required (credit: Mikael Eriksson).

Equally, the TOP in the Tally CTE technically needs an ORDER BY as well. Without that, SQL Server would be quite within its rights to return any numbered rows from the set, in any order.

Both correctness enhancements can be incorporated in the code without affecting the execution plan or performance.

Taking the Digits Only function as an example, I have added suitable ORDER BY clauses below:


CREATE OR ALTER FUNCTION dbo.DigitsOnlyEE
(
  @pString varchar(8000)
) 
RETURNS table
WITH SCHEMABINDING 
AS 
RETURN
 WITH 
  E1(N) AS 
  (
   SELECT N 
   FROM 
   (
    VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)
   ) AS X(N)
  ),
  Tally(N) AS 
  (
   SELECT TOP (CONVERT(bigint, LEN(@pString))) 
        rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
   FROM E1 AS a
   CROSS JOIN E1 AS b
   CROSS JOIN E1 AS c
   CROSS JOIN E1 AS d 
   ORDER BY rn
  ) 
  SELECT 
   DigitsOnly = 
   (
    SELECT 
      SUBSTRING(@pString,T.N,1)
    FROM Tally AS T
    WHERE 
      ((ASCII(SUBSTRING(@pString,T.N,1)) - 48) & 0x7FFF) < 10
    ORDER BY 
      T.N
    FOR XML PATH('')
   );


I'm pretty sure that's not necessary in this case because of the original ORDER BY (strange as it seems).  If possible, I'd like to see someone come up with a case where the original ORDER BY can have the screws put to it externally even if intentionally.