Concatenating strings containing NULL values

Question

I want to concatenate a string

I want output like this:

NEX-SYM-VIM-CRE

If the input is NEX-NULL-NULL-VRE, it comes out to be NEX---CRE or ---CRE or NEX--- as i have replaced NULL with -

But concatenation to get a final result like NEX-SYM is not coming

Vincent Vancalbergh · Accepted Answer · 2011-03-22 09:14:09Z

2

Something like this?

ISNULL(NEX,'-') + '-' + ISNULL(SYM,'-') + '-' + ISNULL(VIM,'-') + '-' + ISNULL(CRE,'-')

answered Mar 22, 2011 at 9:14

Vincent Vancalbergh

3,3372 gold badges24 silver badges25 bronze badges

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

1 Comment

Vincent Vancalbergh Over a year ago

Lol, i'm not sure I did, but sometimes you gotta go with your gut :)

onedaywhen · Accepted Answer · 2011-03-22 10:00:31Z

2

Always add a - delimiting character to the right but only when there is a value:

NULLIF(
       COALESCE(NEX + '-', '') 
          + COALESCE(SYM + '-', '') 
          + COALESCE(VIM + '-', '') 
          + COALESCE(CRE + '-', ''), ''
      )

then you always need to trim the last character (will be a - delimiting character) unless the result is NULL.

answered Mar 22, 2011 at 10:00

onedaywhen

57.4k12 gold badges103 silver badges142 bronze badges

Collectives™ on Stack Overflow

Concatenating strings containing NULL values

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related