Post reply

Using REPLACE with a string where I need to replace %anystring% with some new string

  • August 14, 2012 at 9:21 pm

    #1525870

    SQLKnowItAll (8/14/2012)

    The actual execution plans are exactly the same for each and IO is the same. Interestingly enough... Every time I run this, the third one performs slightly better on time. I saw the same result when I did a second CROSS JOIN for the data load and took TOP 1000000. Now, we are only talking a couple hundred ms, but it was consistent. Thoughts?

    Thoughts? Yes. What do you want to have happen when there are more than one instance of paired percent signs in the same string?

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

  • August 15, 2012 at 5:10 am

    #1525949

    Jeff Moden (8/14/2012)

    SQLKnowItAll (8/14/2012)

    The actual execution plans are exactly the same for each and IO is the same. Interestingly enough... Every time I run this, the third one performs slightly better on time. I saw the same result when I did a second CROSS JOIN for the data load and took TOP 1000000. Now, we are only talking a couple hundred ms, but it was consistent. Thoughts?

    Thoughts? Yes. What do you want to have happen when there are more than one instance of paired percent signs in the same string?

    Ah ha! I was waiting for that. Luckily, the business rules do not allow for that. If a second 'variable' is added (that's what the paired percent signs indicate), the programmer will have to introducea new character for thier own program anyway. However, the current design makes a second very unlikely in the context that it is used.

    Jared
    CE - Microsoft

  • August 15, 2012 at 2:23 pm

    #1526219

    SQLKnowItAll (8/15/2012)

    Jeff Moden (8/14/2012)

    SQLKnowItAll (8/14/2012)

    The actual execution plans are exactly the same for each and IO is the same. Interestingly enough... Every time I run this, the third one performs slightly better on time. I saw the same result when I did a second CROSS JOIN for the data load and took TOP 1000000. Now, we are only talking a couple hundred ms, but it was consistent. Thoughts?

    Thoughts? Yes. What do you want to have happen when there are more than one instance of paired percent signs in the same string?

    Ah ha! I was waiting for that. Luckily, the business rules do not allow for that. If a second 'variable' is added (that's what the paired percent signs indicate), the programmer will have to introducea new character for thier own program anyway. However, the current design makes a second very unlikely in the context that it is used.

    Should not, then, the code return an error if there is more than one instance?

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

  • August 15, 2012 at 2:40 pm

    #1526229

    Jeff Moden (8/15/2012)

    SQLKnowItAll (8/15/2012)

    Jeff Moden (8/14/2012)

    SQLKnowItAll (8/14/2012)

    The actual execution plans are exactly the same for each and IO is the same. Interestingly enough... Every time I run this, the third one performs slightly better on time. I saw the same result when I did a second CROSS JOIN for the data load and took TOP 1000000. Now, we are only talking a couple hundred ms, but it was consistent. Thoughts?

    Thoughts? Yes. What do you want to have happen when there are more than one instance of paired percent signs in the same string?

    Ah ha! I was waiting for that. Luckily, the business rules do not allow for that. If a second 'variable' is added (that's what the paired percent signs indicate), the programmer will have to introduce a new character for their own program anyway. However, the current design makes a second very unlikely in the context that it is used.

    Should not, then, the code return an error if there is more than one instance?

    So far, no. Discussion has occurred on this and the curent thought is that a second instance, should it ever exist, will be left alone.

    Jared
    CE - Microsoft

  • August 15, 2012 at 3:06 pm

    #1526242

    SQLKnowItAll (8/15/2012)

    Jeff Moden (8/15/2012)

    SQLKnowItAll (8/15/2012)

    Jeff Moden (8/14/2012)

    SQLKnowItAll (8/14/2012)

    The actual execution plans are exactly the same for each and IO is the same. Interestingly enough... Every time I run this, the third one performs slightly better on time. I saw the same result when I did a second CROSS JOIN for the data load and took TOP 1000000. Now, we are only talking a couple hundred ms, but it was consistent. Thoughts?

    Thoughts? Yes. What do you want to have happen when there are more than one instance of paired percent signs in the same string?

    Ah ha! I was waiting for that. Luckily, the business rules do not allow for that. If a second 'variable' is added (that's what the paired percent signs indicate), the programmer will have to introduce a new character for their own program anyway. However, the current design makes a second very unlikely in the context that it is used.

    Should not, then, the code return an error if there is more than one instance?

    So far, no. Discussion has occurred on this and the curent thought is that a second instance, should it ever exist, will be left alone.

    No problem. Just wanted to make sure because "We're all in this together and I'm pullin' for ya!"

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

  • August 15, 2012 at 7:44 pm

    #1526309

    Jeff Moden (8/15/2012)

    SQLKnowItAll (8/15/2012)

    Jeff Moden (8/15/2012)

    SQLKnowItAll (8/15/2012)

    Jeff Moden (8/14/2012)

    SQLKnowItAll (8/14/2012)

    The actual execution plans are exactly the same for each and IO is the same. Interestingly enough... Every time I run this, the third one performs slightly better on time. I saw the same result when I did a second CROSS JOIN for the data load and took TOP 1000000. Now, we are only talking a couple hundred ms, but it was consistent. Thoughts?

    Thoughts? Yes. What do you want to have happen when there are more than one instance of paired percent signs in the same string?

    Ah ha! I was waiting for that. Luckily, the business rules do not allow for that. If a second 'variable' is added (that's what the paired percent signs indicate), the programmer will have to introduce a new character for their own program anyway. However, the current design makes a second very unlikely in the context that it is used.

    Should not, then, the code return an error if there is more than one instance?

    So far, no. Discussion has occurred on this and the current thought is that a second instance, should it ever exist, will be left alone.

    No problem. Just wanted to make sure because "We're all in this together and I'm pullin' for ya!"

    Well thanks for your concern! Always glad to have you challenge me!

    Jared
    CE - Microsoft

  • August 16, 2012 at 6:26 am

    #1526521

    On the subject of second and succeeding instances (3rd, 4rd, 5rd, etcrd.), running the function repeatedly would handle that gracefully.

    Using the Stuff() version would allow the same %variable% to be replaced by different values if it appears more than once, if that's what you want. Using the Replace() version would replace re-occurences of the same %variable% with the same new value, while allowing other %variable% strings to have consistent but different values.

    For example:

    This is a %string%, which needs to have %string% replaced, by %string%.

    Using Stuff(), each instance of %string% could have a different replacement value, depending on sequential position.

    This is a %string1%, which needs to have %string1% replaced, by %string2%.

    Using Replace() would allow both %string1% instances to be replaced with the same value, and %string2% to be replaced with a different value.

    If you had something like the first example, where it's all %string%, and you want to replace all of them with the same value, then the Replace() version could handle that in one pass, while Stuff() would require three passes to get them all.

    Easy enough to solve multiple variables. You just have to define what end result you want from what inputs.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 7 posts - 16 through 21 (of 21 total)

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