null value append string in sql-server

Question

I have a query

update quotestable set quotestags= quotestags +', Panchatantra' where quotesid=1374

This works only if quotestags is not null. If it is null then it doesnot update. What is the way around?

Dave94 · Accepted Answer · 2017-08-08 07:29:34Z

3

I think this is a better solution:

UPDATE quotestable
SET quotestags = CASE 
        WHEN quotestags IS NULL
            THEN 'Panchatantra'
        ELSE quotestags + ', Panchatantra'
        END
WHERE quotesid = 1374

Because you don't want to append those ', ' when quotestags is null.

answered Aug 8, 2017 at 7:29

Dave94

711 silver badge8 bronze badges

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

Comments

HoneyBadger · Accepted Answer · 2017-08-08 07:26:38Z

3

You could use:

update quotestable set quotestags= COALESCE(quotestags, '') +', Panchatantra' where quotesid=1374

This replaces the null value with an empty string to concatenate.

By the way, if quotestags is null, the update is still executed, but null + [value] = null, so the effect is identical to the situation before update.

answered Aug 8, 2017 at 7:26

HoneyBadger

15.2k3 gold badges36 silver badges50 bronze badges

3 Comments

Richard Over a year ago

I would prefer ISNULL for this case but COALESCE also works.

HoneyBadger Over a year ago

@Richard, just out of interest, why would you prefer isnull? I always prefer coalesce because it is ANSI, hence more portable.

Richard Over a year ago

Habit mostly, plus IMHO it is more clearly named (ANSI has never been an issue on the systems I've worked on: ability to port was never a requirement).

Collectives™ on Stack Overflow

null value append string in sql-server

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related