May 18, 2010 at 9:24 pm
Sorry for the delay... here's an "Inline Table Valued Function" (iTVF) with a bit of flexibility built in. It's not a panacea but it certainly works for this particular problem.
First, here's the test data that ColdCoffee was kind enough to build up for us...
--===== Conditionally drop the test table, rebuild it, and populate it
IF OBJECT_ID('TempDB..#YourTable') IS NOT NULL
DROP TABLE #YourTable
;
CREATE TABLE #YourTable
(
String VARCHAR(128)
)
;
INSERT INTO #YourTable
(String)
SELECT 'PO_x0020_416G_x002F_484A' UNION ALL
SELECT 'S_x0020_8292' UNION ALL
SELECT 'ABC_x0020_DEF_x0020_GHI_x002f_' UNION ALL
SELECT 'A1B2_x0020_D3E44_x0020_5FR'
;
Next, a function that uses the magic of a Tally table so we can get away from a fairly slow scalar UDF and go with a nice fast iTVF instead...
CREATE FUNCTION dbo.RemoveSplitPattern
(
@pString VARCHAR(8000),
@pDelimiter VARCHAR(1),
@pPattern VARCHAR(8000)
)
RETURNS TABLE
AS
RETURN
SELECT LTRIM
(
( --== Split the items out, reject any having the pattern, reassemble with spaces
SELECT ' ' + SUBSTRING(@pString, t.N, CHARINDEX(@pDelimiter, @pString + @pDelimiter, t.N) - t.N)
FROM dbo.Tally t
WHERE N BETWEEN 1 AND LEN(@pString)
AND SUBSTRING(@pDelimiter + @pString, t.N, 1) = @pDelimiter
AND SUBSTRING(@pString, t.N, CHARINDEX(@pDelimiter, @pString + @pDelimiter, t.N) - t.N) NOT LIKE @pPattern
ORDER BY t.N
FOR XML PATH('')
)
) AS ITEM
;
... and now, the test... Notice how iTVF's can be used very nicely by a Cross Apply (thank you Paul White)...
SELECT yt.String, itvf.Item
FROM #YourTable yt
CROSS APPLY
(
SELECT Item
FROM dbo.RemoveSplitPattern(yt.String,'_','x[0-9A-F][0-9A-F][0-9A-F][0-9A-F]')
) itvf
;
Here's the result set from the test...
STRING Item
----------------------------------- -----------------------------------
PO_x0020_416G_x002F_484A PO 416G 484A
S_x0020_8292 S 8292
ABC_x0020_DEF_x0020_GHI_x002f_ ABC DEF GHI
A1B2_x0020_D3E44_x0020_5FR A1B2 D3E44 5FR
(4 row(s) affected)
Just in case there's a doubt about how much faster both the Tally table is than a While Loop and an iTVF is faster than a scaler UDF, check out the following timings on my faithful 8 year old desktop box for just 8K rows...
--========== Tally/iTVF function ==========
(8192 row(s) affected)
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 485 ms.
--========== While Loop/Scalar function ==========
(8192 row(s) affected)
SQL Server Execution Times:
CPU time = 27812 ms, elapsed time = 54157 ms.
Let's put that in simple terms... the Tally/iTVF uses 177 times less CPU and is 110 times faster in duration than the While Loop/Scalar UDF combination. Heh... post those numbers as percentages and you get 17700% and 11000% respectively.
If you don't know what a Tally table is or how it works to replace certain While Loops, please see the following article...
http://www.sqlservercentral.com/articles/T-SQL/62867/
--Jeff Moden
Change is inevitable... Change for the better is not.
May 18, 2010 at 10:56 pm
Jeff Moden (5/18/2010)
niteshrajgopal (5/18/2010)
Hi again, is there anyway I can add to your reputation or something on this forum:-)Heh... not to worry. CC is building his reputation where it counts. 😉
Wow, am absolutely honored, flattered, pampered, etc etc etc.. Wow.. Thanks Jeff! :blush:
Task 1: Get good name! Progress: Partly done! Need to improvise! 😀
Thanks a lot!
May 18, 2010 at 11:25 pm
Jeff Moden (5/18/2010)
Sorry for the delay... here's an "Inline Table Valued Function" (iTVF) with a bit of flexibility built in. It's not a panacea but it certainly works for this particular problem.
--========== Tally/iTVF function ==========
(8192 row(s) affected)
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 485 ms.
--========== While Loop/Scalar function ==========
(8192 row(s) affected)
SQL Server Execution Times:
CPU time = 27812 ms, elapsed time = 54157 ms.
Let's put that in simple terms... the Tally/iTVF uses 177 times less CPU and is 110 times faster in duration than the While Loop/Scalar UDF combination. Heh... post those numbers as percentages and you get 17700% and 11000% respectively.
If you don't know what a Tally table is or how it works to replace certain While Loops, please see the following article...
Ah there it is, the fastest set-based code! Nitesh, as i said, i very well know there is a fast alternative for the scalar function i gave and here u go, u get it from THE MAN of SSC, our man Jeff!
Thanks Jeff for this wonderful code! I went into my chest!
May 19, 2010 at 12:33 am
ColdCoffee (5/18/2010)
Jeff Moden (5/18/2010)
niteshrajgopal (5/18/2010)
Hi again, is there anyway I can add to your reputation or something on this forum:-)Heh... not to worry. CC is building his reputation where it counts. 😉
Wow, am absolutely honored, flattered, pampered, etc etc etc.. Wow.. Thanks Jeff! :blush:
Task 1: Get good name! Progress: Partly done! Need to improvise! 😀
Thanks a lot!
Heh... nope. 😛 Task 1 is always "Get good code". The good name will always follow. 😉
You always try even if you think it may be wrong and you always learn, CC... you can't ask much more of a person. You'll do very well. Keep up the good work.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 19, 2010 at 12:38 am
ColdCoffee (5/18/2010)
THE MAN of SSC
Heh... thanks for the compliment but there's good people on this forum that can improve on that code. Everytime I get lucky and pull something off like this, I've got about a thousand people to thank from this forum.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 19, 2010 at 3:26 am
wow:-D thanks Jeff. you really know your stuff. Might only get to test this tomorrow though. Will get back to you. It looks awesome
May 19, 2010 at 8:16 am
Heh... thanks niteshrajgopal...
It might be more appropriate to say that I know "other people's stuff". I didn't invent any of these methods... I just get lucky putting them together. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
May 19, 2010 at 9:31 am
Jeff Moden (5/19/2010)
ColdCoffee (5/18/2010)
Jeff Moden (5/18/2010)
niteshrajgopal (5/18/2010)
Hi again, is there anyway I can add to your reputation or something on this forum:-)Heh... not to worry. CC is building his reputation where it counts. 😉
Wow, am absolutely honored, flattered, pampered, etc etc etc.. Wow.. Thanks Jeff! :blush:
Task 1: Get good name! Progress: Partly done! Need to improvise! 😀
Thanks a lot!
Heh... nope. 😛 Task 1 is always "Get good code". The good name will always follow. 😉
You always try even if you think it may be wrong and you always learn, CC... you can't ask much more of a person. You'll do very well. Keep up the good work.
Thanks Jeffy; i will try as much possible to get the best codes out of my knowledge 🙂
May 19, 2010 at 4:35 pm
ColdCoffee (5/19/2010)
Thanks Jeffy;
Ummm.... not to be a stick in the mud but two people in the known universe get to call me "Jeffy"... and you're not either one of them. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 19, 2010 at 8:37 pm
Jeff Moden (5/19/2010)
ColdCoffee (5/19/2010)
Thanks Jeffy;Ummm.... not to be a stick in the mud but two people in the known universe get to call me "Jeffy"... and you're not either one of them. 😉
Oh, sorry Jeff , my apologies ! :pinch:
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply