asked on

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?

ASKER CERTIFIED SOLUTION

Cluskitt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

knightEknight

there may be a better way, but this will work:

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

ralmada

just do like this

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

Philippe Renaud

ASKER

Thanks. totally forgot the case when..

ralmada

oh well.

Cluskitt

ralmada, yours could never work, because
'-' + NULL
is always
'-'
so it would never be null.

knightEknight

actually '-' + null is always null

Cluskitt

Oh. I stand corrected then. Ralmada's code is a good alternative, then.

Philippe Renaud

ASKER

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