Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 do you have a StripNonNumeric ITVF function? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, June 25, 2014 6:07 AM
 SSChampion Group: General Forum Members Last Login: Yesterday @ 2:44 PM Points: 13,570, Visits: 34,543
 we all know Inline Table Value Functions perform at least a couple of order of magnitude faster than a scalar function, but there are some functions I have in my collection that are scalar, that , so far, I don't have an ITVF equivilent for.I've got a slow running process, that i found is using a scalar function to strip out non numeric characters; my knee jerk reaction was "I'll just replace that and change to a cross apply /outer apply to do the same thing!Q, and then realized i wasn't making any headway on modifying an existing example to become an ITVF.before i reinvent the wheel, does anyone have one?here's an example of a scalar function, that is featuring a Tally Table to build the "right" string. run that on a million rows, and it bogs things down a wee bit. `IF OBJECT_ID('[dbo].[StripNonNumeric]') IS NOT NULL DROP FUNCTION [dbo].[StripNonNumeric] GOCREATE FUNCTION StripNonNumeric(@OriginalText VARCHAR(8000))RETURNS VARCHAR(8000) BEGIN DECLARE @CleanedText VARCHAR(8000) ;WITH tally (N) as(SELECT TOP 10000 row_number() OVER (ORDER BY sc1.id) FROM Master.dbo.SysColumns sc1 CROSS JOIN Master.dbo.SysColumns sc2)SELECT @CleanedText = ISNULL(@CleanedText,'') + CASE --ascii numbers are 48(for '0') thru 57 (for '9') WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) BETWEEN 48 AND 57 THEN SUBSTRING(@OriginalText,Tally.N,1) ELSE '' END FROM tally WHERE Tally.N <= LEN(@OriginalText) RETURN @CleanedText END` Lowell--There is no spoon, and there's no default ORDER BY in sql server either.Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1585850
 Posted Wednesday, June 25, 2014 8:21 AM This worked for the OP
 SSChampion Group: General Forum Members Last Login: Thursday, July 2, 2015 2:57 PM Points: 14,244, Visits: 13,936
 Taking your fine code as a starting point I came up with this. I didn't test against a million rows but I think maybe I will do that now.`CREATE FUNCTION StripNonNumeric_itvf(@OriginalText VARCHAR(8000))RETURNS TABLE WITH SCHEMABINDING ASreturnWITH E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)), E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max Tally(N) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 ) select STUFF(( SELECT SUBSTRING(@OriginalText, t.N, 1) FROM tally t WHERE ASCII(SUBSTRING(@OriginalText, t.N, 1)) BETWEEN 48 AND 57 FOR XML PATH('')), 1 ,0 , '') as CleanedText ` _______________________________________________________________Need help? Help us help you. Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.Need to split a string? Try Jeff Moden's splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)
Post #1585923
 Posted Wednesday, June 25, 2014 9:17 AM
 SSChampion Group: General Forum Members Last Login: Yesterday @ 2:44 PM Points: 13,570, Visits: 34,543
 smacking myself in the head! I didn't think of using FOR XML to handle the concatenation; that, sir, is beautiful and inspired.You'd think i'd know better, so double kudos to you.i'll test it on my process, but I'm pretty sure it'll be quicker than what is there now. Lowell--There is no spoon, and there's no default ORDER BY in sql server either.Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1585961
 Posted Wednesday, June 25, 2014 9:21 AM
 SSChampion Group: General Forum Members Last Login: Thursday, July 2, 2015 2:57 PM Points: 14,244, Visits: 13,936
 Lowell (6/25/2014)smacking myself in the head! I didn't think of using FOR XML to handle the concatenation; that, sir, is beautiful and inspired.You'd think i'd know better, so double kudos to you.i'll test it on my process, but I'm pretty sure it'll be quicker than what is there now.Awesome. Glad that will work. Sometime it is easier to see a solution when aren't familiar with the current process. I have added that to my own toolbox as that could definitely come in handy at some point. _______________________________________________________________Need help? Help us help you. Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.Need to split a string? Try Jeff Moden's splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)
Post #1585965
 Posted Wednesday, June 25, 2014 11:23 AM
 Ten Centuries Group: General Forum Members Last Login: Yesterday @ 10:42 PM Points: 1,143, Visits: 4,299
Gents

First: Dang it Sean - you beet me to it! I had a very similar solution but you posted yours first. That said, I did do a performance test and ran into something I cannot figure out.

on my laptop (Windows 8, 2 cores/4 logical cpu/16GB run SQL Server 2014 Ent) I ran your solution (with results on) and it takes 31 seconds complete (my test harness data below). I tried to improve the time in your query but had no success.

I have an ngrams function that I use for this kind of thing. Below is the function with my notes on how to use it:

`IF OBJECT_ID('tempdb.dbo.nGrams8K') IS NOT NULL DROP FUNCTION dbo.nGrams8KGOCREATE FUNCTION dbo.nGrams8K (@string varchar(8000), @k int)/********************************************************************Created by:	     Alan BursteinCreated on:      3/10/2014Last Updated on: 5/22/2015n-gram defined:  In the fields of computational linguistics and probability,   an n-gram is a contiguous sequence of n items from a given   sequence of text or speech. The items can be phonemes, syllables,  letters, words or base pairs according to the application.  For more information see: http://en.wikipedia.org/wiki/N-gramUse:  Outputs a stream of tokens based on an input string.  Similar to mdq.nGrams:  http://msdn.microsoft.com/en-us/library/ff487027(v=sql.105).aspx.  Except it only returns characters as long as K.  nGrams8K also includes the position of the "Gram" in the string.  Examples of how to use included as comments after the code.********************************************************************/RETURNS TABLE WITH SCHEMABINDING ASRETURN  WITH   E1(N) AS (SELECT 1 FROM (VALUES (null),(null),(null),(null),(null)) x(n)),  E3(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b CROSS JOIN E1 c),  iTally(N) AS   (	SELECT TOP (LEN(@string)-(@k-1)) CHECKSUM(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))	FROM E3 a CROSS JOIN E3 b  )  SELECT	position = N,	token = SUBSTRING(@string,N,@k)  FROM iTally;GO/********************************************************************(1) Basic Use------------------------------------------------------------- (A) With @k as 1,2 & 3:  SELECT position, token FROM dbo.nGrams8K('abcdefg',1);  SELECT position, token FROM dbo.nGrams8K('abcdefg',2);  SELECT position, token FROM dbo.nGrams8K('abcdefg',3);-- (B) Using variables  DECLARE 	@string varchar(20) = 'abcdefg12345', @tokenLen tinyint = 3;  SELECT position, token FROM dbo.nGrams8K(@string,@tokenLen)  GO(2) Character Count (including 0 Counts)------------------------------------------------------------- (A) Basic character count  SELECT token = ISNULL(token,'Total:'), count(*)   FROM dbo.nGrams8K(newid(),1)  GROUP BY token  WITH ROLLUP;	-- Added rollup for display-- (B) Character Account including gaps  DECLARE 	@alphabet VARCHAR(26)='ABCDEFGHIJKLMNOPQRSTUVWXYZ',	@string VARCHAR(100)='The quick green fox jumps over the lazy dog and the lazy dogs.';  SELECT a.token, COUNT(b.token) ttl  FROM dbo.nGrams8K(@alphabet,1) a  LEFT JOIN dbo.nGrams8K(@string,1) b ON a.token=b.token  GROUP BY a.token  ORDER BY a.token; -- not required, for display only  GO-- (C) Let's try French ordered by most frequent  DECLARE	@alphabet VARCHAR(36)='abcdefghijklmnopqrstuvwxyzéèçëòôöùàâ',	@string VARCHAR(100)='Le renard vert rapide saute par dessus le chien paresseux et le chien paresseux juste posé là.';  WITH charcount AS  (	SELECT a.token, COUNT(b.token) ttl	FROM dbo.nGrams8K(@alphabet,1) a	LEFT JOIN dbo.nGrams8K(@string,1) b ON a.token=b.token	GROUP BY a.token  )  SELECT * FROM charcount ORDER BY ttl DESC;  GO(3) a *SIMPLE* word count -----------------------------------------------------------  DECLARE @string varchar(100)='THE QUICK Green FOX JUMPED OVER THE LAZY DOGS BACK';  SELECT @string AS string, count(*)+1 AS words  FROM dbo.nGrams8K(@string,1)  WHERE [token]=' '  GO(4) search for occurances and location of a substring-----------------------------------------------------------  DECLARE 	@string VARCHAR(100)='The green fox jumps over the dog and the dog just laid there.',	@searchString VARCHAR(100)='The';  SELECT position, token AS searchString  FROM dbo.nGrams8K(@string,LEN(@searchstring)) b  WHERE token=@searchString;  GO(5) Strip non-numeric characters from a string-----------------------------------------------------------  DECLARE @string varchar(100) = 'abc123xyz555xxx999!';  WITH stripNonNumeric(cleanstring) AS  (	SELECT token+''	FROM dbo.nGrams8K(@string,1)	WHERE token LIKE '[0-9]'	FOR XML PATH('')  )  SELECT cleanstring   FROM stripNonNumeric;(6) Find all occurances of a pattern in a string------------------------------------------------------------  DECLARE 	@string varchar(100) = 'zz12x345xxx555abc1234zz5xxx',	@pat varchar(100) = '[a-z][a-z][0-9]',	@len int = 3;  SELECT start_pos = position, token  FROM dbo.nGrams8K(@string,@len)  WHERE token LIKE @patSELECT * FROM dbo.findpat8k(@string,@pat,3);GO(7) Find the longest common substring between 2 strings-------------------------------------------------------------- (A) The Function  IF EXISTS  (		SELECT * FROM INFORMATION_SCHEMA.ROUTINES	WHERE ROUTINE_SCHEMA='dbo' AND ROUTINE_NAME = 'lcss'  )  DROP FUNCTION dbo.lcss;  GO  CREATE FUNCTION dbo.lcss(@string1 varchar(100), @string2 varchar(100))  RETURNS TABLE WITH SCHEMABINDING AS RETURN  WITH iTally(N) AS  (	SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))	FROM dbo.nGrams8K	(	  CASE WHEN LEN(@string1)<=LEN(@string2) THEN @string1 	  ELSE @string2 END,1	)  )  SELECT TOP (1) with ties token  FROM iTally  CROSS APPLY   dbo.nGrams8K  (	CASE WHEN LEN(@string1)<=LEN(@string2) THEN @string1	ELSE @string2 END, n  )  WHERE N <= 	CASE WHEN LEN(@string1)<=LEN(@string2) THEN LEN(@string1) 	ELSE LEN(@string2) END  AND charindex  (	token, 	CASE WHEN LEN(@string1)<=LEN(@string2) THEN @string2 	ELSE @string1 END  ) > 0  ORDER BY len(token) DESC;  GO-- (B) example of how to use lcss  DECLARE 	@string1 varchar(100) = '999xxyyyzaa99xxyyyzz',	@string2 varchar(100) = '000xxxyyyzzz';  SELECT string1 = @string1, string2 = @string2, token  FROM dbo.lcss(@string1, @string2);********************************************************************/GO`

I ran the following 10K Row Test:

`SET NOCOUNT ON;DBCC FREEPROCCACHE WITH NO_INFOMSGSDBCC DROPCLEANBUFFERS WITH NO_INFOMSGSSET STATISTICS IO ON;SET STATISTICS TIME ON;	PRINT 'Sean'+char(13);	SELECT CleanedText 	FROM #strings	CROSS APPLY dbo.StripNonNumeric_itvf(string);	PRINT 'Alan'+char(13);	SELECT CleanedText	FROM #strings	CROSS APPLY dbo.StripNonNumeric_itvf_ajb(string);SET STATISTICS IO OFF;SET STATISTICS TIME OFF;`

... and here's the results:

`Sean SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 0 ms.Table '#strings____________________________________________________________________________________________________________00000000004C'. Scan count 1, logical reads 63, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times:   CPU time = 31172 ms,  elapsed time = 31213 ms.Alan SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 0 ms.Table '#strings____________________________________________________________________________________________________________00000000004C'. Scan count 1, logical reads 63, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times:   CPU time = 328 ms,  elapsed time = 384 ms. SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 0 ms.`

I have no idea why my NGrams solution is so much faster considering that we are essentially doing the same thing. I included the query plans for both.

Lowell: I hope this helps.

-- Alan Burstein

Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001

My blog

Post Attachments
 alan.sqlplan (1 view, 102.21 KB)
 sean.sqlplan (1 view, 102.21 KB)
Post #1586023
 Posted Wednesday, June 25, 2014 12:27 PM
 SSChampion Group: General Forum Members Last Login: Thursday, July 2, 2015 2:57 PM Points: 14,244, Visits: 13,936
 Alan.B (6/25/2014)GentsFirst: Dang it Sean - you beet me to it! I had a very similar solution but you posted yours first. That said, I did do a performance test and ran into something I cannot figure out. Nice. I didn't see your test data and haven't had a chance to roll my own yet. I am guessing that the big difference is that my code uses STUFF and yours does not.If you still have your test harness handy try with this.`ALTER FUNCTION [dbo].[StripNonNumeric_itvf](@OriginalText VARCHAR(8000))RETURNS TABLE WITH SCHEMABINDING ASreturnWITH E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)), E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max Tally(N) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 ) , clean_text(CleanedText) as ( SELECT SUBSTRING(@OriginalText, t.N, 1) FROM tally t WHERE ASCII(SUBSTRING(@OriginalText, t.N, 1)) BETWEEN 48 AND 57 FOR XML PATH('') ) select CleanedText from clean_text` _______________________________________________________________Need help? Help us help you. Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.Need to split a string? Try Jeff Moden's splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)
Post #1586049
 Posted Wednesday, June 25, 2014 1:45 PM
 Ten Centuries Group: General Forum Members Last Login: Yesterday @ 10:42 PM Points: 1,143, Visits: 4,299
 Sean Lange (6/25/2014)Alan.B (6/25/2014)GentsFirst: Dang it Sean - you beet me to it! I had a very similar solution but you posted yours first. That said, I did do a performance test and ran into something I cannot figure out. Nice. I didn't see your test data and haven't had a chance to roll my own yet. I am guessing that the big difference is that my code uses STUFF and yours does not.If you still have your test harness handy try with this.`ALTER FUNCTION [dbo].[StripNonNumeric_itvf](@OriginalText VARCHAR(8000))RETURNS TABLE WITH SCHEMABINDING ASreturnWITH E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)), E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max Tally(N) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 ) , clean_text(CleanedText) as ( SELECT SUBSTRING(@OriginalText, t.N, 1) FROM tally t WHERE ASCII(SUBSTRING(@OriginalText, t.N, 1)) BETWEEN 48 AND 57 FOR XML PATH('') ) select CleanedText from clean_text`What you posted was getting the same results (31 seconds)... I added this to the where clause:AND n <= len(@OriginalText)[/code]Final Version`ALTER FUNCTION [dbo].[StripNonNumeric_itvf_sean2](@OriginalText VARCHAR(8000))RETURNS TABLE WITH SCHEMABINDING ASreturnWITH E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)), E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max Tally(N) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 ) , clean_text(CleanedText) as ( SELECT SUBSTRING(@OriginalText, t.N, 1) FROM tally t WHERE ASCII(SUBSTRING(@OriginalText, t.N, 1)) BETWEEN 48 AND 57 AND n <= len(@OriginalText) -- added by ajb FOR XML PATH('') ) select CleanedText from clean_textGO`It now runs in Milliseconds, same speed as mine. ~30 seconds for a million rows on my system using the test harness I posted earlier.Edit: Some of my code got turned into HTML tags. Underlined my change. -- Alan BursteinRead this article for best practices on asking questions.Need to split a string? Try this (Jeff Moden)Need a pattern-based string spitter? Try this (Dwain Camps)"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001My blog
Post #1586092
 Posted Wednesday, June 25, 2014 1:53 PM
 SSChampion Group: General Forum Members Last Login: Thursday, July 2, 2015 2:57 PM Points: 14,244, Visits: 13,936
 Alan.B (6/25/2014)Sean Lange (6/25/2014)Alan.B (6/25/2014)GentsFirst: Dang it Sean - you beet me to it! I had a very similar solution but you posted yours first. That said, I did do a performance test and ran into something I cannot figure out. Nice. I didn't see your test data and haven't had a chance to roll my own yet. I am guessing that the big difference is that my code uses STUFF and yours does not.If you still have your test harness handy try with this.`ALTER FUNCTION [dbo].[StripNonNumeric_itvf](@OriginalText VARCHAR(8000))RETURNS TABLE WITH SCHEMABINDING ASreturnWITH E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)), E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max Tally(N) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 ) , clean_text(CleanedText) as ( SELECT SUBSTRING(@OriginalText, t.N, 1) FROM tally t WHERE ASCII(SUBSTRING(@OriginalText, t.N, 1)) BETWEEN 48 AND 57 FOR XML PATH('') ) select CleanedText from clean_text`What you posted was getting the same results (31 seconds)... I added this to the where clause:`AND n <= len(@OriginalText)`Final Version`ALTER FUNCTION [dbo].[StripNonNumeric_itvf_sean2](@OriginalText VARCHAR(8000))RETURNS TABLE WITH SCHEMABINDING ASreturnWITH E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)), E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max Tally(N) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 ) , clean_text(CleanedText) as ( SELECT SUBSTRING(@OriginalText, t.N, 1) FROM tally t WHERE ASCII(SUBSTRING(@OriginalText, t.N, 1)) BETWEEN 48 AND 57 AND n <= len(@OriginalText) -- added by ajb FOR XML PATH('') ) select CleanedText from clean_textGO`It now runs in Milliseconds, same speed as mine. ~30 seconds for a million rows on my system using the test harness I posted earlier.Edit: Some of my code got turned into HTML tags.That makes sense. No point evaluating all the 10,000 rows when they won't meet the condition anyway. I suspect if we changed my original (with the STUFF) it would perform much the same with the inclusion of the additional predicate in the where clause. Awesome work Alan. _______________________________________________________________Need help? Help us help you. Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.Need to split a string? Try Jeff Moden's splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)
Post #1586099
 Posted Wednesday, June 25, 2014 2:17 PM
 Ten Centuries Group: General Forum Members Last Login: Yesterday @ 10:42 PM Points: 1,143, Visits: 4,299
 That makes sense. No point evaluating all the 10,000 rows when they won't meet the condition anyway. I suspect if we changed my original (with the STUFF) it would perform much the same with the inclusion of the additional predicate in the where clause. Awesome work Alan. Thanks Sean! -- Alan BursteinRead this article for best practices on asking questions.Need to split a string? Try this (Jeff Moden)Need a pattern-based string spitter? Try this (Dwain Camps)"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001My blog
Post #1586125
 Posted Wednesday, June 25, 2014 2:19 PM
 Ten Centuries Group: General Forum Members Last Login: Thursday, July 2, 2015 4:46 PM Points: 1,193, Visits: 3,237
 HiInterestingly enough I tried a similar test on 2012 for the originals and got elapsed times that so close as to be the same. It may have been the strings I was using though.Bring on a PATREPLACE function ChrisM's pattern splitter from Dwain's article may also be able to be used here`select cleanedstringFROM #strings s CROSS APPLY ( SELECT Item + '' FROM PatternSplitCM(string,'[^0-9]') WHERE MATCHED = 0 FOR XML PATH ('') ) x (cleanedstring)`
Post #1586126

 Permissions

 Copyright © 2002-2015 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.