Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

REPLACE() and NULL Expand / Collapse
Author
Message
Posted Monday, July 12, 2010 8:05 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 19, 2012 8:39 AM
Points: 595, Visits: 1,226
Nice question.

Converting oxygen into carbon dioxide, since 1955.

Post #950774
Posted Monday, July 12, 2010 9:17 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, July 18, 2014 3:37 PM
Points: 3,123, Visits: 1,256
Anyone got any ideas why this function was made to work this way? Makes no sense to me.


Post #950826
Posted Monday, July 12, 2010 9:53 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 6:26 AM
Points: 1,521, Visits: 3,036
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
Post #950862
Posted Monday, July 12, 2010 10:07 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, December 7, 2010 12:55 AM
Points: 771, Visits: 504
Good question.

Also, thanks John Arnott for the explanation. It makes sense.
Post #950873
Posted Monday, July 12, 2010 10:24 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, July 18, 2014 3:37 PM
Points: 3,123, Visits: 1,256
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.



Post #950891
Posted Monday, July 12, 2010 10:54 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 6:26 AM
Points: 1,521, Visits: 3,036
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.
Post #950907
Posted Monday, July 12, 2010 11:11 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, July 18, 2014 3:37 PM
Points: 3,123, Visits: 1,256
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.



Post #950917
Posted Monday, July 12, 2010 11:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 2,326, Visits: 2,654
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


-------------------
"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
Post #950942
Posted Monday, July 12, 2010 12:27 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
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
Post #950964
Posted Monday, July 12, 2010 12:57 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 11, 2014 12:42 PM
Points: 1,380, Visits: 2,682
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
Post #950986
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse