SQL Server: replace by NULL

Question

SELECT REPLACE('hello', 'e', NULL) 
-- returns NULL

SELECT REPLACE('hello', '5', NULL) 
-- also returns NULL -- here I'd expect it does nothing since there is no '5' in 'hello'

The documentation clearly states:

Returns NULL if any one of the arguments is NULL.

So the behavior is explained.

Is there a workaround, meaning if a pattern is found in a string like 'e' in hello ; a NULL value is emitted?

If the pattern is not found in hello, then what should the output be? — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 23, 2021 at 10:02
Side note: SQL Server 2008 has been completely unsupported for well over 2 years; you really should be looking at upgrade paths. — Thom A
– Thom A ♦, Commented Dec 23, 2021 at 10:17
@Tim Biegeleisen ; if it is not found, it should return hello. If it is found, it should return NULL. — TimBer
– TimBer, Commented Dec 23, 2021 at 10:47

Thom A · Accepted Answer · 2021-12-23 10:04:13Z

3

Seems like REPLACE isn't what you are after. One method instead would be using a CASE and a LIKE:

SELECT CASE WHEN 'hello' NOT LIKE '%5%' THEN 'hello' END;

Alternatively you could use CHARINDEX:

SELECT CASE WHEN CHARINDEX('5','hello') = 0 THEN 'hello' END;

answered Dec 23, 2021 at 10:04

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

TimBer Over a year ago

I just tried ; but you cannot force the result to be NULL in a case :(

Thom A Over a year ago

What do you mean, @TimBer ? Both the above will return NULL if the search character is found. db<>fiddle

Collectives™ on Stack Overflow

SQL Server: replace by NULL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related