Post reply

WILD REPLACE ?

  • February 9, 2010 at 2:31 am

    #1115991

    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?

    C# Gnu
    ____________________________________________________


    Excel Database Tasks : efficient business data processing
    Version 3.3 released February 2015

  • February 9, 2010 at 2:53 am

    #1116000

    BTW I figured also if passing pattern to dream function then I would have to specify additional charaters to avoid clash with meta characters

    '%[b][[/b]comment:#%[b]][/b]'

    where [b][[/b] and [b]][/b] represent single characters '[' and ']'

    C# Gnu
    ____________________________________________________


    Excel Database Tasks : efficient business data processing
    Version 3.3 released February 2015

  • February 9, 2010 at 5:49 am

    #1116057

    C# Screw (2/9/2010)

    BTW I figured also if passing pattern to dream function then I would have to specify additional charaters to avoid clash with meta characters

    '%[b][[/b]comment:#%[b]][/b]'

    where [b][[/b] and [b]][/b] represent single characters '[' and ']'

    You don't need (or want) the []].... only the [[]. Look in Books Online under "Like".

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

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

  • February 16, 2010 at 9:52 am

    #1119351

    Thanks Jeff

    appologies for the delay...

    When I get a moment I might try and build a function that fully supports the PATINDEX sytax.

    C# Gnu
    ____________________________________________________


    Excel Database Tasks : efficient business data processing
    Version 3.3 released February 2015

  • February 16, 2010 at 10:34 am

    #1119386

    Hmm, this all seems like the kind of thing that a CLR UDF should burn through like C4.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • February 16, 2010 at 10:40 am

    #1119391

    In fact, for the longer-strings, an abbreviated Boyer-Moore Search and Replace (or an unabbreviated one for really big strings) should be freaky fast.

    (edit: fixed spelling)

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • February 16, 2010 at 10:59 am

    #1119402

    Well I am reluctant to post this but what the heck - the result of a late night brainwave :

    I thought I had cracked it by finding the end position of the pattern by reversing the string and reversing the search pattern.

    It does in fact work if the search pattern only contains % characters.

    I was so jubilent - but then despondent as I realised that reversing something like '[a-b]' gives a pattern of ']b-a['. :w00t:

    At that point I gave up & went to bed !

    Here is the late night night code for what its worth:

    (Hi Barry I am wondering what is C4?)

    ALTER PROCEDURE [dbo].[up_WildReplace]

    (

    @Text VARCHAR(MAX) ,

    @Pattern VARCHAR(500) ,

    @ReplaceWithText VARCHAR(MAX)

    )

    --

    -- c#Screw 09-02-10

    -- Search @SomeText for text matching @Pattern

    -- Replace all occurances of patterb with ReplaceWithText

    -- Designed so @Pattern can contain PATINDEX meta cards %[] etc

    --

    --RETURNS VARCHAR(MAX)

    -- intended to be a function once developement finished

    AS

    BEGIN

    DECLARE @Occurance INT ,

    @CopyOfText VARCHAR(MAX) ,

    @Result VARCHAR(MAX) ,

    @EndPos INT

    -- Create Array of Start & End Positions

    DECLARE @Locations TABLE

    (

    Occurance INT ,

    StartPosition INT ,

    EndPosition INT

    )

    -- Populate Start Positions

    SET @CopyOfText = @Text

    SET @Occurance = 1

    WHILE PATINDEX(@Pattern, @CopyOfText) > 0

    BEGIN

    INSERT INTO @Locations

    ( Occurance ,

    StartPosition

    )

    VALUES ( @Occurance ,

    PATINDEX(@Pattern, @CopyOfText)

    )

    -- remove 1st char of pattern to prevent match again

    SELECT @CopyOfText = STUFF(@CopyOfText,

    PATINDEX(@Pattern, @CopyOfText), 1,

    CHAR(9))

    SET @Occurance = @Occurance + 1

    END

    -- Populate End Positions

    SET @CopyOfText = @Text

    SET @Occurance = @Occurance - 1

    WHILE PATINDEX(REVERSE(@Pattern), REVERSE(@CopyOfText)) > 0

    BEGIN

    SET @EndPos = DATALENGTH(@CopyOfText)

    - ( PATINDEX(REVERSE(@Pattern), REVERSE(@CopyOfText)) - 2 )

    UPDATE @Locations

    SET EndPosition = @EndPos

    WHERE Occurance = @Occurance

    SET @Occurance = @Occurance - 1

    -- remove 1st char of pattern to prevent match again

    SELECT @CopyOfText = STUFF(@CopyOfText, @EndPos - 1, 1,CHAR(9))

    END

    SELECT *

    FROM @Locations

    -- Stuff replacement text between start & end markers

    SELECT @Text = STUFF(@Text, StartPosition,

    EndPosition - StartPosition,

    CHAR(9) + REPLICATE(CHAR(27),

    EndPosition - StartPosition- 1))

    FROM @Locations

    SELECT @Text = REPLACE(@Text, CHAR(27), '')

    SELECT @Text = REPLACE(@Text, CHAR(9), @ReplaceWithText)

    RETURN @Text

    END

    C# Gnu
    ____________________________________________________


    Excel Database Tasks : efficient business data processing
    Version 3.3 released February 2015

  • February 16, 2010 at 11:34 am

    #1119424

    C# Screw (2/16/2010)

    (Hi Barry I am wondering what is C4?)

    C4 is a plastic explosive with a particularly high "burn rate" or (more properly) detonation velocity of 28,900 kph.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • February 16, 2010 at 11:37 am

    #1119429

    Cool! not Channel 4 then !! 🙂

    C# Gnu
    ____________________________________________________


    Excel Database Tasks : efficient business data processing
    Version 3.3 released February 2015

  • February 16, 2010 at 1:10 pm

    #1119488

    C# Screw (2/16/2010)

    Thanks Jeff

    appologies for the delay...

    When I get a moment I might try and build a function that fully supports the PATINDEX sytax.

    I agree with Barry on this... a CLR UDF would probably be the right thing to do here... of course, any DB that requires a Wild Card Replace might need some of that C4 in a redesign process. 😀

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

  • February 16, 2010 at 2:35 pm

    #1119583

    Jeff Moden (2/16/2010)

    C# Screw (2/16/2010)

    Thanks Jeff

    appologies for the delay...

    When I get a moment I might try and build a function that fully supports the PATINDEX sytax.

    I agree with Barry on this... a CLR UDF would probably be the right thing to do here... of course, any DB that requires a Wild Card Replace might need some of that C4 in a redesign process. 😀

    We have varchar fields that contain SQL code, many thousands of rows.

    The database design is very cool indeed, consists of questions and answers with sql code embedded in both questions and answers supporting the business logic. Different answers send the user off to other questions depending on underlying data etc.

    Maintaining it though can be interesting!, being able to search and replace is helpful. I achieved it ok with the function I coded/posted earlier fn_STUFF_Between_Tags 😎 but supporting patindex style would be cooler :cool::cool:

    C# Gnu
    ____________________________________________________


    Excel Database Tasks : efficient business data processing
    Version 3.3 released February 2015

  • February 16, 2010 at 6:34 pm

    #1119748

    It does sound like fun. Thanks for the feedback. 🙂

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

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

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