REPLACE behavior

  • ronmoses (3/6/2014)


    Richard Warr (3/6/2014)


    It's anything but logical behaviour and you can only get the question correct if you know that caveat on the REPLACE function (or if you just run the code).

    Developer A: "We have a bug in this function. Want me to fix it?"

    Developer B: "Nah, just document it as normal behavior."

    Developer A: "Done."

    Developer B: Consider it a feature. 😀

    Thanks for the great question.



    Everything is awesome!

  • Interesting question. 🙂

  • nice and easy.

    thanks.

  • +1

  • Thanks Auke. I liked the question. Do you have more?

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • Interesting, Thanks for the question.

  • Even though this is a simple one to remember, when strings are taken from a table ample are the chances that this behavior would be forgotten.

  • @Anoo

    This is exactly what happened to me and how I found out about this behaviour.

  • @auke - And together we learn by sharing our mistakes. Thank you for sharing the question.

  • auke.teeninga (3/6/2014)


    In many programming languages the third argument wouldn't even be evalutated if the second argument didn't appear in the first argument.

    All these will just return 'ABC':

    SELECT REPLACE('ABC', 'Z', 1)

    SELECT REPLACE('ABC', 'Z', CURRENT_TIMESTAMP)

    SELECT REPLACE('ABC', 'Z', '#$@%')

    This one will return 'ABC' half of the time and NULL the other half.

    SELECT REPLACE('ABC', 'Z', CASE WHEN RAND() < 0.5 THEN 'A' END)

    I thought of making the problem more complex, but I figured that would result in more people just running the query instead of answering first! 😉

    So, although the actual QotD is straightforward, one has to be careful to guard against having anything in the REPLACE statement that evaluates to NULL.

  • ronmoses (3/6/2014)


    Richard Warr (3/6/2014)


    It's anything but logical behaviour and you can only get the question correct if you know that caveat on the REPLACE function (or if you just run the code).

    Developer A: "We have a bug in this function. Want me to fix it?"

    Developer B: "Nah, just document it as normal behavior."

    Developer A: "Done."

    +1

Viewing 11 posts - 16 through 25 (of 25 total)

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