January 4, 2019 at 12:09 am
Comments posted to this topic are about the item REPLACE and NULLs
God is real, unless declared integer.
January 4, 2019 at 7:14 am
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/
January 4, 2019 at 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
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
January 4, 2019 at 8:36 am
nice reminder
---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know
January 4, 2019 at 10:39 am
webrunner - Friday, January 4, 2019 8:18 AMThanks 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/
January 4, 2019 at 10:49 am
Sean Lange - Friday, January 4, 2019 10:39 AMwebrunner - Friday, January 4, 2019 8:18 AMThanks 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 asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
January 4, 2019 at 12:12 pm
webrunner - Friday, January 4, 2019 10:49 AMSean Lange - Friday, January 4, 2019 10:39 AMwebrunner - Friday, January 4, 2019 8:18 AMThanks 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/
January 4, 2019 at 12:25 pm
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 asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
January 4, 2019 at 11:03 pm
t.franz - Friday, January 4, 2019 12:09 AMComments posted to this topic are about the item REPLACE and NULLs
Nice googly stumped me
Saravanan
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy