REPLACE and NULLs

  • Thomas Franz

    Hall of Fame

    Points: 3566

    Comments posted to this topic are about the item REPLACE and NULLs

    God is real, unless declared integer.

  • Sean Lange

    SSC Guru

    Points: 286482

    Nice easy question to end the week on. It seems a number of people are still confused about NULL.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • webrunner

    One Orange Chip

    Points: 29987

    Thanks for the instructive question. But why is it that the REPLACE() function returns NULL even if the search parameter is NULL? Why was that decision made? Just curious.

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and says Can I join you?
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Shayn Thomas

    SSCertifiable

    Points: 5429

    nice reminder

    ---------------------------------------------------------------------------------------
    The more you know, the more you know that you dont know

  • Sean Lange

    SSC Guru

    Points: 286482

    webrunner - Friday, January 4, 2019 8:18 AM

    Thanks for the instructive question. But why is it that the REPLACE() function returns NULL even if the search parameter is NULL? Why was that decision made? Just curious.

    - webrunner

    Because by the nature of NULL it would be impossible to truly know if it matched or not. Nothing ever equals NULL so how could it find NULL in a string? On that end you can't have NULL be a portion of a string value. What should it return? What you are saying is that it should search NULL and return something?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • webrunner

    One Orange Chip

    Points: 29987

    Sean Lange - Friday, January 4, 2019 10:39 AM

    webrunner - Friday, January 4, 2019 8:18 AM

    Thanks for the instructive question. But why is it that the REPLACE() function returns NULL even if the search parameter is NULL? Why was that decision made? Just curious.

    - webrunner

    Because by the nature of NULL it would be impossible to truly know if it matched or not. Nothing ever equals NULL so how could it find NULL in a string? On that end you can't have NULL be a portion of a string value. What should it return? What you are saying is that it should search NULL and return something?

    I guess my thinking is that REPLACE() is intended to allow the query writer to ask that a value be replaced with some other value.
    I understand that NULL is not a value as such but it can be assigned to be the contents of a column or variable or function parameter. So I'm not sure why the REPLACE() function could not allow a NULL value to be replaced with a string. In other words, say "string_expression" evaluates to NULL. Then the second parameter "string_Pattern" could be passed in as NULL and the string_replacement could be 'NONE' similar to ISNULL(). 

    REPLACE ( string_expression , string_pattern , string_replacement )

    I suppose since ISNULL() already does that and that "string_expression" can never be non-NULL if any part of the string passed in is NULL (as when trying to concatenate a string with a NULL) then MS figured there was no point in allowing it in the function? I'm not saying REPLACE() should behave this way, just that I don't know if it is per se the only way to have the function behave.

    Thanks.
    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and says Can I join you?
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Sean Lange

    SSC Guru

    Points: 286482

    webrunner - Friday, January 4, 2019 10:49 AM

    Sean Lange - Friday, January 4, 2019 10:39 AM

    webrunner - Friday, January 4, 2019 8:18 AM

    Thanks for the instructive question. But why is it that the REPLACE() function returns NULL even if the search parameter is NULL? Why was that decision made? Just curious.

    - webrunner

    Because by the nature of NULL it would be impossible to truly know if it matched or not. Nothing ever equals NULL so how could it find NULL in a string? On that end you can't have NULL be a portion of a string value. What should it return? What you are saying is that it should search NULL and return something?

    I guess my thinking is that REPLACE() is intended to allow the query writer to ask that a value be replaced with some other value.
    I understand that NULL is not a value as such but it can be assigned to be the contents of a column or variable or function parameter. So I'm not sure why the REPLACE() function could not allow a NULL value to be replaced with a string. In other words, say "string_expression" evaluates to NULL. Then the second parameter "string_Pattern" could be passed in as NULL and the string_replacement could be 'NONE' similar to ISNULL(). 

    REPLACE ( string_expression , string_pattern , string_replacement )

    I suppose since ISNULL() already does that and that "string_expression" can never be non-NULL if any part of the string passed in is NULL (as when trying to concatenate a string with a NULL) then MS figured there was no point in allowing it in the function? I'm not saying REPLACE() should behave this way, just that I don't know if it is per se the only way to have the function behave.

    Thanks.
    - webrunner

    But you can't have part of a string be NULL. That is the point I was making. If you passed in two parameters and did a concatenation the first thing that happens is does a conversion of the two strings. And string1 + string2 is always NULL when either of them are NULL. If the function did anything else it would be inconsistent with how everything else regarding strings works.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • webrunner

    One Orange Chip

    Points: 29987

    Yes, thanks. Seems like that is what ISNULL() is for, as I mentioned. And explains why REPLACE() was made to return NULL if any parameter is NULL.

    Thanks again.

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and says Can I join you?
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • saravanatn

    SSCarpal Tunnel

    Points: 4530

    t.franz - Friday, January 4, 2019 12:09 AM

    Comments posted to this topic are about the item REPLACE and NULLs

    Nice googly stumped me

    Saravanan

Viewing 9 posts - 1 through 9 (of 9 total)

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