Tricky REPLACE problem

  • New week, new ideas and a new solution (it might not be the best, but it's new).

    I haven't compared it with Dwain's solution.

    It's prepared to change one or two characters with multiple appearances on each string.

    DECLARE @test-2TABLE(

    stringvarchar(100))

    INSERT @test-2

    SELECT 'L@*K@*H@/324' AS string

    UNION ALL SELECT '(AF#-AR#)*(Y#+BB#)'

    UNION ALL SELECT 'TT#*(L#+D@)*L@'

    UNION ALL SELECT '((AE#-AR#)*(Y#+A#))/Y#';

    DECLARE@Searchedvarchar(2),

    @newvarchar(2),

    @Lengthint

    SET @Searched = 'L'

    SET @new = 'XY'

    SET @Length = LEN( @Searched)

    WITH Data AS(

    SELECTCAST( '+' + string AS varchar( 101)) string,

    1 AS n

    FROM @test-2

    UNION ALL

    SELECT CAST( CASE WHEN SUBSTRING( d.string, n, @Length) = @Searched

    AND SUBSTRING( d.string, n - 1, 1) LIKE '[^A-Z]'

    AND SUBSTRING( d.string, n + @Length, 1) IN ( '#', '@', '&')

    THEN STUFF( d.string, n, @Length, @new)

    ELSE d.string END AS varchar( 101)),

    n + 1 AS n

    FROM Data d

    WHERE n < LEN( d.string))

    SELECT STUFF( d.string,1,1,'') AS string

    FROM Data d

    WHERE LEN( d.string) = d.n

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • dwain.c, Mark, and Luis, thank you very much for your solutions. All three are workable and really quite brilliant. Ultimately I believe I'll be going with Luis's suggestion, as it's the only one I don't seem to be able to break based on the length of the "variable" values I feed it. I know I can tweak the other two to behave the same, but Luis's appears solid right out of the box.

    I greatly appreciate the effort all three of you put into this. I only hope I can return the favor some day.

    Thanks!

    ron

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • Well, I could really use an annual pass to Disney World 😀

    I'm really glad I could help you.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • ronmoses (9/10/2012)


    dwain.c, Mark, and Luis, thank you very much for your solutions. All three are workable and really quite brilliant. Ultimately I believe I'll be going with Luis's suggestion, as it's the only one I don't seem to be able to break based on the length of the "variable" values I feed it. I know I can tweak the other two to behave the same, but Luis's appears solid right out of the box.

    I greatly appreciate the effort all three of you put into this. I only hope I can return the favor some day.

    Thanks!

    ron

    Mark's and Luis's solutions were definitely much less ugly, so I can't blame you.

    At least mine was in the running for awhile.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain.c (9/10/2012)


    At least mine was in the running for awhile.

    Truth be told, I was literally in the process of typing up my response declaring you the winner, when Luis's code came in. I had to try it out, just to be fair, and the rest is history. 🙂

    Thanks!

    Ron

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

Viewing 5 posts - 16 through 19 (of 19 total)

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