T-SQL Replace part of a not null column with NULL generates error

Question

I have table variable and all its columns can not be null (NOT NULL definition for each):

DECLARE @SampleTable 
(
   ,SampleColumnID nvarchar(400) NOT NULL PRIMARY KEY
   ,SampleColumnText nvarchar(max) NOT NULL
)

I have done some operation with this variable and initialize the "SampleColumnText" with some text.

Then I try to replace some part of it with text return from other function. What happens is that the function returns NULL in some cases, so I this code generates me error:

REPLACE(SampleColumnText , '{*}', @InitByFunctionText)

WHERE @InitByFunctionText is NULL this time.

So, is it normal error to be generated as I am replacing only part of the text with NULL, not the whole text?

Damien_The_Unbeliever · Accepted Answer · 2012-08-06 07:23:59Z

2

This is expected behaviour. REPLACE:

Returns NULL if any one of the arguments is NULL.

If you want to replace it with an empty string (which is not the same as NULL), you can use COALESCE:

REPLACE(SampleColumnText , '{*}', COALESCE(@InitByFunctionText,''))

answered Aug 6, 2012 at 7:23

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

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

Comments

XN16 · Accepted Answer · 2012-08-06 07:23:27Z

2

I had a similar thing recently and the following got around this issue:

REPLACE(SampleColumnText , '{*}', ISNULL(@InitByFunctionText, ''))

answered Aug 6, 2012 at 7:23

XN16

5,88916 gold badges52 silver badges76 bronze badges

Collectives™ on Stack Overflow

T-SQL Replace part of a not null column with NULL generates error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related