Remove Pattern in sql query

Question

Remove Pattern in sql query

Viewing 10 posts - 16 through 24 (of 24 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

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

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)

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

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!

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

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...
http://www.sqlservercentral.com/articles/T-SQL/62867/

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!

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

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

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)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

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

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)

niteshrajgopal SSCommitted Points: 1877 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

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

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)

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 8

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 🙂

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

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

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)

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 10

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: