REPLACE() and NULL

Question

Post reply

REPLACE() and NULL

ronmoses@gmail.com

SSCarpal Tunnel

Points: 4480
More actions
July 11, 2010 at 8:15 am

#226711

Comments posted to this topic are about the item REPLACE() and NULL
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown

Viewing 15 posts - 1 through 15 (of 28 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Nice question ....

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Nice question - thanks.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 3

Nice one...

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

ziangij SSCrazy Eights Points: 8066 More actions · Answer 4

thnx nice one 🙂

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

as suggested by you... even this returns the same output

Kari Suresh Hall of Fame Points: 3712 More actions · Answer 5

[p]I learnt new thing today. Thanks Ronmoses.[/p]

KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

Abrar Ahmad_ SSCarpal Tunnel Points: 4244 More actions · Answer 6

[font="Verdana"]Goodish! simple and informative!

[/font]

jimkam04 Grasshopper Points: 18 More actions · Answer 7

Good question. i like this function. Have used it

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 8

A simple question, but a usefull one nonetheless, especially if you use the function with variables as input parameters.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 9

This is one of those things that can be a real gotcha if you don't know about it, and put in code to handle it ahead of time. Thanks.

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 10

Nice question.

Converting oxygen into carbon dioxide, since 1955.

Kevin SSCarpal Tunnel Points: 4962 More actions · Answer 11

Anyone got any ideas why this function was made to work this way? Makes no sense to me.

john.arnott SSChampion Points: 11882 More actions · Answer 12

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. SSCommitted Points: 1570 More actions · Answer 13

Good question.

Also, thanks John Arnott for the explanation. It makes sense.

Kevin SSCarpal Tunnel Points: 4962 More actions · Answer 14

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.