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