SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


REPLACE() and NULL


REPLACE() and NULL

Author
Message
Steve Cullen
Steve Cullen
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1321 Visits: 1226
Nice question.

Converting oxygen into carbon dioxide, since 1955.


kevin.l.williams
kevin.l.williams
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3635 Visits: 1323
Anyone got any ideas why this function was made to work this way? Makes no sense to me.



john.arnott
john.arnott
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3510 Visits: 3059
kevin.l.williams (7/12/2010)
Anyone got any ideas why this function was made to work this way? Makes no sense to me.

I cannot answer that authoritatively, but consider that it would make no sense to try to replace part of a string with NULL either, so perhaps the decision was made to check the types of all three parameters first before doing the matching.

--
edit: fix typo
KevinC.
KevinC.
SSC Eights!
SSC Eights! (875 reputation)SSC Eights! (875 reputation)SSC Eights! (875 reputation)SSC Eights! (875 reputation)SSC Eights! (875 reputation)SSC Eights! (875 reputation)SSC Eights! (875 reputation)SSC Eights! (875 reputation)

Group: General Forum Members
Points: 875 Visits: 504
Good question.

Also, thanks John Arnott for the explanation. It makes sense.
kevin.l.williams
kevin.l.williams
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3635 Visits: 1323
KevinC. (7/12/2010)
It makes sense.


Really? Would it have been too difficult to return the string unmodified if there was a NULL detected? In the way it is now, a small bug can be disastrous, potentially wiping out critical data.



john.arnott
john.arnott
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3510 Visits: 3059
kevin.l.williams (7/12/2010)
...Would it have been too difficult to return the string unmodified if there was a NULL detected? ....

Actually, I think the answer to that would go back to the more general question of handling NULL consistently. Of course it wouldn't have been difficult to have the function do that, but would it have been the right thing to do?

The QOD not only calls our attention to this specific behavior in the REPLACE() function, but also to the general issue of understanding and programing to handle NULL. The proposal to have REPLACE() return an unmodified string would essentially depend on treating a NULL third parameter as an empty string, which it is not.
kevin.l.williams
kevin.l.williams
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3635 Visits: 1323
john.arnott (7/12/2010)The QOD not only calls our attention to this specific behavior in the REPLACE() function, but also to the general issue of understanding and programing to handle NULL.


I see your point. unknown in = unknown out.

Harsh reality in some cases.



webrunner
webrunner
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7629 Visits: 3998
john.arnott (7/12/2010)
kevin.l.williams (7/12/2010)
Anyone got any ideas why this function was made to work this way? Makes no sense to me.

I cannot answer that authoritatively, but consider that it would make no sense to try to replace part of a string with NULL either, so perhaps the decision was made to check the types of all three parameters first before doing the matching.

--
edit: fix typo


I guess my question is why does the REPLACE function replace anything if the string pattern (second parameter) isn't in the string expression (first parameter)?

For example, this doesn't return 'bbb':

SELECT REPLACE('Hello world', 'zzz', 'bbb');

So why should

SELECT REPLACE('Hello world', 'zzz', NULL);

return NULL?

Thanks in advance for any help,
webrunner

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"Operator! Give me the number for 911!" - Homer Simpson

"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
Tom Garth
Tom Garth
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1939 Visits: 1499
Good question, and a good reminder. Thanks.

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Trey Staker
Trey Staker
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1590 Visits: 2788
Nice question. I only knew it because I've run into it before.

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search