Home Forums SQL Server 2008 T-SQL (SS2K8) WILD REPLACE ? RE: WILD REPLACE ?

  • February 9, 2010 at 5:51 am

    #1116060

    C# Screw (2/9/2010)

    Thanks Jeff

    your PATINDEX idea seems a step closer to a dream 'WildReplace' function which might be :

    CREATE FUNCTION dbo.fn_WildReplace(@SomeText varchar(max),

    @Pattern varchar(500), @ReplaceWith varchar(max))

    .....

    Edit: corrected syntax

    Usage :

    SELECT dbo.fn_WildReplace('Without a doubt SQLCentral is

    home to finest SQL folk[comment:#grovel].', '%[comment:#%]', '')

    --returning:

    --Without a doubt SQLCentral is home to finest SQL folk.

    The function doesn't specify any start or end markers, just a pattern to find and replace.

    I can see we can use PATINDEX to find start location of the pattern, but I canny figure out how you would determine the end location of the pattern?

    It's easy if you want to do that. Just do a split on the % in a single pattern to replace the left and right patterns. I was just being a bit lazy... didn't want to have to worry about writing code to escape the % if someone needed to look for it.

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