Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

do you have a StripNonNumeric ITVF function? Expand / Collapse
Author
Message
Posted Wednesday, June 25, 2014 6:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:20 AM
Points: 12,916, Visits: 32,080
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]
GO
CREATE 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 Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:06 AM
Points: 13,449, Visits: 12,311
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 AS
return

WITH
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

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:20 AM
Points: 12,916, Visits: 32,080
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

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:06 AM
Points: 13,449, Visits: 12,311
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


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 581, Visits: 2,709
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:

ALTER FUNCTION [dbo].[nGrams8K]
(
@string VARCHAR(8000),
@n TINYINT,
@pad BIT=0
)
/*
Created by: Alan Burstein
Created on: 3/10/2014
Updated on: 5/20/2014

Use: Outputs a stream of tokens based on an input string.
Works just like mdq.nGrams; see http://msdn.microsoft.com/en-us/library/ff487027(v=sql.105).aspx.

n-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.

To better understand N-Grams
see: http://en.wikipedia.org/wiki/N-gram
*/
RETURNS TABLE
WITH SCHEMABINDING
AS

RETURN
WITH
E1(n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(n)),
E2(n) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b),
E4(n) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b),
iTally(n) AS
(
SELECT TOP (len(@string)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM E4
),
NewString(NewString) AS
(
SELECT REPLICATE(CASE @pad WHEN 0 THEN '' ELSE ' ' END,@n-1)+@string+
REPLICATE(CASE @pad WHEN 0 THEN '' ELSE ' ' END,@n-1)
)
SELECT n AS sequence,
SUBSTRING(NewString,n,@n) AS token
FROM iTally
CROSS APPLY NewString
WHERE n < ((@n)+LEN(@string));

/*
------------------------------------------------------------
-- (1) Basic Use
------------------------------------------------------------
SELECT [sequence], token
FROM dbo.nGrams8K('abcdefg',1,0);

SELECT [sequence], token
FROM dbo.nGrams8K('abcdefg',2,0);

SELECT [sequence], token
FROM dbo.nGrams8K('abcdefg',3,0);

SELECT [sequence], token
FROM dbo.nGrams8K('abcdefg',3,1);

-- note the query plan for each. The power is coming from an index
-- also note how many rows are produced: len(@string+(@n-1))
-- lastly, you can trim as needed when padding=1

-- (2) With a variable
------------------------------------------------------------
DECLARE @string varchar(20) = 'abcdefg';

SELECT [sequence], token
FROM dbo.nGrams8K(@string,1,0);
GO


-- (3) An on-the-fly alphabet (this will come in handy in a moment)
------------------------------------------------------------
DECLARE @alphabet VARCHAR(26)='ABCDEFGHIJKLMNOPQRSTUVWXYZ';

SELECT [sequence], token
FROM dbo.nGrams8K(@alphabet,1,0);
GO

-- ***!!! Add multiple alphabets here in a table


-- (4) Character Count
------------------------------------------------------------
DECLARE @string VARCHAR(100)='The quick green fox jumps over the lazy dog and the lazy dog just laid there.',
@alphabet VARCHAR(26)='ABCDEFGHIJKLMNOPQRSTUVWXYZ';

SELECT a.token, COUNT(b.token) ttl
FROM dbo.nGrams8K(@alphabet,1,0) a
LEFT JOIN dbo.nGrams8K(@string,1,0) b ON a.token=b.token
GROUP BY a.token
ORDER BY a.token;
GO


-- (5) a *SIMPLE* word count
------------------------------------------------------------
DECLARE @string varchar(100)='THE QUICK Green FOX JUMPED OVER THE LAZY DOGS BACK';

-- (A) note this query:
SELECT * FROM dbo.nGrams8K(@string,1,0) a;

-- (B) note this query:
SELECT * FROM dbo.nGrams8K(@string,1,0) a WHERE [token]=' ';

-- (C) and now the word count (@string included for presentation)
SELECT @string AS string,
count(*)+1 AS words
FROM dbo.nGrams8K(@string,1,0) a
WHERE [token]=' '
GO
-- query plan still linaer!


-- (6) search for the number of occurances of a word
------------------------------------------------------------
DECLARE @string VARCHAR(100)='The quick green fox jumps over the lazy dog and the lazy dog just laid there.',
@alphabet VARCHAR(26)='ABCDEFGHIJKLMNOPQRSTUVWXYZ',
@searchString VARCHAR(100)='The';

-- (5a) by location
SELECT sequence-(LEN(@searchstring)) AS location,
token AS searchString
FROM dbo.nGrams8K(@string,LEN(@searchstring+' ')+1,0) b
WHERE token=@searchString;

-- (2b) get total
SELECT @string AS string,
@searchString AS searchString,
COUNT(*) AS ttl
FROM dbo.nGrams8K(@string,LEN(@searchstring+' ')+1,0) b
WHERE token=@searchString;


-- (6) Complex String Metric Algorithms
------------------------------------------------------------

-- (6a) hamming distance between two strings:
DECLARE @string1 varchar(8000) = 'xxxxyyyzzz',
@string2 varchar(8000) = 'xxxxyyzzzz';

--DECLARE @string1 varchar(8000) = replicate('$',7950)+'xxxxyyyzzz',
-- @string2 varchar(8000) = replicate('$',7950)+'xxxxyyzzzz';


set statistics io on;
set statistics time on;

SELECT string1 = @string1,
string2 = @string2,
hamming_distance = count(*)
FROM dbo.nGrams8K(@string1,1,0) s1
CROSS APPLY dbo.nGrams8K(@string2,1,0) s2
WHERE s1.sequence = s2.sequence
AND s1.token <> s2.token

set statistics io off;
set statistics time off;
GO

-- (6b) inner joing between 2 strings
--(can be used to speed up other string metrics such as the longest common subsequence)
DECLARE @string1 varchar(100)='xxxx123yyyy456zzzz',
@string2 varchar(100)='xx789yy000zz';

WITH
s1(string1) AS
(
SELECT [token]+''
FROM dbo.nGrams8K(@string1,1,0)
WHERE charindex([token],@string2)<>0
ORDER BY [sequence]
FOR XML PATH('')
),
s2(string2) AS
(
SELECT [token]+''
FROM dbo.nGrams8K(@string1,1,0)
WHERE charindex([token],@string2)<>0
ORDER BY [sequence]
FOR XML PATH('')
)
SELECT string1, string2
FROM s1
CROSS APPLY s2;
*/
GO

Using that function I came up with this:

CREATE FUNCTION StripNonNumeric_itvf_ajb(@OriginalText VARCHAR(8000))
RETURNS TABLE WITH SCHEMABINDING AS
return

WITH ngrams AS
(
SELECT n = [sequence],
c = token
FROM dbo.nGrams8K(@OriginalText,1,1)
),
clean_txt(CleanedText) AS
(
SELECT c+''
FROM ngrams
WHERE ascii(substring(@OriginalText,n,1)) BETWEEN 48 AND 57
FOR XML PATH('')
)
SELECT CleanedText
FROM clean_txt;
GO

I ran the following 10K Row Test:

SET NOCOUNT ON;

DBCC FREEPROCCACHE WITH NO_INFOMSGS
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS

SET 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)
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

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:06 AM
Points: 13,449, Visits: 12,311
Alan.B (6/25/2014)
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.



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 AS
return

WITH
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


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 581, Visits: 2,709
Sean Lange (6/25/2014)
Alan.B (6/25/2014)
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.



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 AS
return

WITH
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 AS
return

WITH
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_text
GO

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 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)
My blog
Post #1586092
Posted Wednesday, June 25, 2014 1:53 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:06 AM
Points: 13,449, Visits: 12,311
Alan.B (6/25/2014)
Sean Lange (6/25/2014)
Alan.B (6/25/2014)
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.



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 AS
return

WITH
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 &lt;= len(@OriginalText)


Final Version
ALTER FUNCTION [dbo].[StripNonNumeric_itvf_sean2](@OriginalText VARCHAR(8000))
RETURNS TABLE WITH SCHEMABINDING AS
return

WITH
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_text
GO

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


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 581, Visits: 2,709
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 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)
My blog
Post #1586125
Posted Wednesday, June 25, 2014 2:19 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:57 PM
Points: 1,023, Visits: 3,069
Hi

Interestingly 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 cleanedstring
FROM #strings s
CROSS APPLY (
SELECT Item + '' FROM PatternSplitCM(string,'[^0-9]') WHERE MATCHED = 0 FOR XML PATH ('')
) x (cleanedstring)

Post #1586126
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse