CODEWITHSUNDEEP
sqlsql-server-2005
user670867
user670867

Reputation: 31

Concatenating strings containing NULL values

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

Upvotes: 1

Views: 238

Answers (2)

onedaywhen
onedaywhen

Reputation: 57023

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.

Upvotes: 2

Vincent Vancalbergh
Vincent Vancalbergh

Reputation: 3327

Something like this?

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

Upvotes: 2

Related Questions