Parse field with Numeric ranges and comma deliminated values

Question

Parse field with Numeric ranges and comma deliminated values

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

thanks Paul; awesome performance on that one; i added that to my toolbox; excellent solution.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Paul White SSC Guru Points: 150468 More actions · Answer 2

Lowell (3/26/2010)
thanks Paul; awesome performance on that one; i added that to my toolbox; excellent solution.

I should point out that it is rather specifically optimized for the test data presented in this thread. The tally solution is likely much more general. In particular, you would need to add an OPTION (MAXRECURSION 0) statement to the final SELECT if any of the ranges exceed 100. The recursive method is not well suited to large ranges, due to inefficient recursive plan elements.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 3

LOL Awesome code regardless...

Both of these are in my toolbox. Great stuff!

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 4

Nice solution Paul. As another variation, I have adapted it slightly to a self-contained CTE block without the table function. You may recognize the final Listed CTE as the same as yours. BTW better luck in the 2nd Test!

SET STATISTICS IO, TIME ON

;

WITH

cteSample AS

(

SELECT '44100-44110, 44150' AS bulkcolumn UNION ALL

SELECT '44115, 44125, 44157' UNION ALL

SELECT '44250-44350, 44128, 44524-44555, 44112, 52144'

)

,

cteSplit

AS

(

SELECT item FROM cteSample

CROSS APPLY

(

SELECT SUBSTRING(bulkcolumn + ',', N, CHARINDEX(',', bulkcolumn + ',', N) - N) AS item

FROM master.dbo.TALLY

WHERE N < LEN(bulkcolumn) + 2 AND SUBSTRING(',' + bulkcolumn + ',', N, 1) = ','

) AS Z

)

,

Listed

AS

(

SELECT value =

CASE WHEN CHARINDEX('-', S.item) = 0 THEN CONVERT(INT, S.item)

ELSE CONVERT(INT, LEFT(S.item, CHARINDEX('-', S.item) - 1)) END,

max_value =

CASE WHEN CHARINDEX('-', S.item) = 0 THEN 0

ELSE CONVERT(INT, SUBSTRING(S.item, CHARINDEX('-', S.item) + 1, 50)) END

FROM cteSplit S

UNION ALL

SELECT L.value + 1,

L.max_value

FROM Listed L

WHERE L.max_value > L.value

)

SELECT value FROM Listed

;

SET STATISTICS IO, TIME OFF

;

--(151 row(s) affected)

--Table 'Worktable'. Scan count 2, logical reads 890, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--Table 'TALLY'. Scan count 3, logical reads 12, 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 = 0 ms, elapsed time = 3 ms.

Paul White SSC Guru Points: 150468 More actions · Answer 5

steve-893342 (3/26/2010)
Nice solution Paul.

Thanks, Steve.

As another variation, I have adapted it slightly to a self-contained CTE block without the table function.

Ok. You did notice that it was an in-line TVF though, right? It gets expanded into the query plan of the caller before optimization just like a parameterized view would. Doesn't really matter though, I guess.

BTW better luck in the 2nd Test!

Thank you. It would be nice to beat the Aussies again. Not many people have managed that this summer.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 6

Yes it would. Maybe Vettori can produce something special in his 100th Test.

Doing all right so far with 4-36 and running out Ponting to boot!

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 7

Sorry folks, can't leave this one alone. I have come up with another variation, which like Wayne's, uses the TALLY table to do both the split and the range evaluation. Obviously it relies on the numbers in the range not exceeding the current 1 million limit of the TALLY table.

SET STATISTICS IO, TIME ON

;

WITH

cteSample AS

(

SELECT '44100-44110, 44150' AS bulkcolumn UNION ALL

SELECT '44115, 44125, 44157' UNION ALL

SELECT '44250-44350, 44128, 44524-44555, 44112, 52144'

)

,

cteSplit

AS

(

SELECT item FROM cteSample

CROSS APPLY

(

SELECT SUBSTRING(bulkcolumn + ',', N, CHARINDEX(',', bulkcolumn + ',', N) - N) AS item

FROM master.dbo.TALLY

WHERE N < LEN(bulkcolumn) + 2 AND SUBSTRING(',' + bulkcolumn + ',', N, 1) = ','

) AS Z

)

SELECT N AS value FROM cteSplit

CROSS APPLY

(

SELECT N from master.dbo.TALLY WHERE N BETWEEN LEFT(item, CHARINDEX('-', item) - 1)

AND REVERSE(LEFT(REVERSE(item), CHARINDEX('-', REVERSE(item)) - 1))

) AS Z

WHERE CHARINDEX('-', item) > 0

UNION ALL SELECT item from cteSplit

WHERE CHARINDEX('-', item) = 0

;

SET STATISTICS IO, TIME OFF

;

--(151 row(s) affected)

--

--Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--Table 'TALLY'. Scan count 9, logical reads 45, 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 = 0 ms, elapsed time = 2 ms.

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 8

Heh... absolutely remarkable. On a bit of a meloncholy note, it's amazing how things have changed in the last 7 or so years on this fine forum. "I remember when" the mantra for such problems was "Oh... for that, you need a cursor" and I had to, yet again, explain what a Tally table was and how it worked. Well done, Gents, and thanks for passing it forward.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WayneS SSC Guru Points: 95461 More actions · Answer 9

steve-893342 (3/27/2010)
Sorry folks, can't leave this one alone. I have come up with another variation, which like Wayne's, uses the TALLY table to do both the split and the range evaluation. Obviously it relies on the numbers in the range not exceeding the current 1 million limit of the TALLY table.
SET STATISTICS IO, TIME ON
;
WITH
cteSample AS
(
SELECT '44100-44110, 44150' AS bulkcolumn UNION ALL
SELECT '44115, 44125, 44157' UNION ALL
SELECT '44250-44350, 44128, 44524-44555, 44112, 52144'
)
,
cteSplit
AS
(
SELECT item FROM cteSample
CROSS APPLY
(
SELECT SUBSTRING(bulkcolumn + ',', N, CHARINDEX(',', bulkcolumn + ',', N) - N) AS item
FROM master.dbo.TALLY
WHERE N < LEN(bulkcolumn) + 2 AND SUBSTRING(',' + bulkcolumn + ',', N, 1) = ','
) AS Z
)
SELECT N AS value FROM cteSplit
CROSS APPLY
(
SELECT N from master.dbo.TALLY WHERE N BETWEEN LEFT(item, CHARINDEX('-', item) - 1)
AND REVERSE(LEFT(REVERSE(item), CHARINDEX('-', REVERSE(item)) - 1))
) AS Z
WHERE CHARINDEX('-', item) > 0
UNION ALL SELECT item from cteSplit
WHERE CHARINDEX('-', item) = 0
;
SET STATISTICS IO, TIME OFF
;
--(151 row(s) affected)
--
--Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Table 'TALLY'. Scan count 9, logical reads 45, 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 = 0 ms, elapsed time = 2 ms.

Steve - this is fabulous! I have got to learn a lot more about cross apply... I see it being used more and more with some pretty awesome performance enhancements.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Paul White SSC Guru Points: 150468 More actions · Answer 10

The article I wrote on APPLY is in the queue for publication.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

WayneS SSC Guru Points: 95461 More actions · Answer 11

Paul White NZ (3/27/2010)
The article I wrote on APPLY is in the queue for publication.

I'm ready for it! Judging solely by some of the posts where you have utilized it, I fully expect this to be one awesome article.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Paul White SSC Guru Points: 150468 More actions · Answer 12

WayneS (3/28/2010)
Paul White NZ (3/27/2010)
The article I wrote on APPLY is in the queue for publication.
I'm ready for it! Judging solely by some of the posts where you have utilized it, I fully expect this to be one awesome article.

Hmm..so no pressure then 😉

I have asked Steve for a publication date.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi