Help with a SQL String

Hello EE,

I have this string in SQL query :

[KeyCode] = Sub_Level_Key1 + '-' + isnull(Sub_Level_Key2, '') + '-' + isnull(Sub_Level_Key3, '')

returns something like:    01-02-03

my problem is if key2 or/and key3 are null it would be like:   01--

I need  a way that if key2 and key3 are null that I do not show the dashes.

so It would be  :   01

or :    01-03
or :    01-06-03

key2 wont be null be key3 is not null.

any idea?
LVL 1
PhilippeRenaudAsked:
Who is Participating?
 
CluskittCommented:
User CASE:
Sub_Level_Key1 + CASE WHEN Sub_Level_Key2 IS NULL THEN '' ELSE '-' + Sub_Level_Key2 END + CASE WHEN Sub_Level_Key3 IS NULL THEN '' ELSE '-' + Sub_Level_Key3 END
0
 
knightEknightCommented:
there may be a better way, but this will work:

[KeyCode] = replace( Sub_Level_Key1 + '-' + isnull(Sub_Level_Key2, '') + '-' + isnull(Sub_Level_Key3, ''), '--', '-' )
0
 
ralmadaCommented:
just do like this

[KeyCode] = Sub_Level_Key1 + isnull('-' + Sub_Level_Key2, '') + isnull('-' + Sub_Level_Key3, '')
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
PhilippeRenaudAuthor Commented:
Thanks. totally forgot the case when..
0
 
ralmadaCommented:
oh well.
0
 
CluskittCommented:
ralmada, yours could never work, because
'-' + NULL
 is always
'-'
so it would never be null.
0
 
knightEknightCommented:
actually '-' + null is always null
0
 
CluskittCommented:
Oh. I stand corrected then. Ralmada's code is a good alternative, then.
0
 
PhilippeRenaudAuthor Commented:
I tried the first comment by the way and it worked and forgot about testing the others
I could had give points to others also.

but thank you for all the help and attention
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.