Remove Pattern in sql query

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

  • 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!

  • 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!

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

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

  • 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

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

  • 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 🙂

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

  • 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