SQL Server: replace by NULL-CodePudding

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?

CodePudding user response：

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;