Search code examples
sql-serverstringsql-server-2000coalesce

sql server query concat string with '-'

In my SQL query, I'm trying to concatenate two strings in my select clause. Here's the expected results:

col A   col B   Result
null    null    &
null    ''      &
null    XYZ     XYC
''      null    &
''      ''      &
''      XYZ     XYC
ABC     null    ABC
ABC     ''      ABC
ABC     XYZ     ABC-XYC

My challenge is this - how do I get the 'dash' to show up for the last scenario and not the others?

Here's my attempt:

DECLARE @ColA as varchar(10)
DECLARE @ColB as varchar(10)

set @ColA = null; set @ColB = null;  select '&'   as 'Expected', COALESCE(NULLIF(COALESCE(@ColA, '') + COALESCE(@ColB, ''), ''), '&') as 'Actual'
set @ColA = null; set @ColB = '';    select '&'   as 'Expected', COALESCE(NULLIF(COALESCE(@ColA, '') + COALESCE(@ColB, ''), ''), '&') as 'Actual'
set @ColA = null; set @ColB = 'XYC'; select 'XYC' as 'Expected', COALESCE(NULLIF(COALESCE(@ColA, '') + COALESCE(@ColB, ''), ''), '&') as 'Actual'
set @ColA = '';   set @ColB = null;  select '&'   as 'Expected', COALESCE(NULLIF(COALESCE(@ColA, '') + COALESCE(@ColB, ''), ''), '&') as 'Actual'
set @ColA = '';   set @ColB = '';    select '&'   as 'Expected', COALESCE(NULLIF(COALESCE(@ColA, '') + COALESCE(@ColB, ''), ''), '&') as 'Actual'
set @ColA = '';   set @ColB = 'XYC'; select 'XYC' as 'Expected', COALESCE(NULLIF(COALESCE(@ColA, '') + COALESCE(@ColB, ''), ''), '&') as 'Actual'
set @ColA = 'ABC';set @ColB = null;  select 'ABC' as 'Expected', COALESCE(NULLIF(COALESCE(@ColA, '') + COALESCE(@ColB, ''), ''), '&') as 'Actual'
set @ColA = 'ABC';set @ColB = '';    select 'ABC' as 'Expected', COALESCE(NULLIF(COALESCE(@ColA, '') + COALESCE(@ColB, ''), ''), '&') as 'Actual'
set @ColA = 'ABC';set @ColB = 'XYC'; select 'ABC-XYC' as 'Expected', COALESCE(NULLIF(COALESCE(@ColA, '') + COALESCE(@ColB, ''), ''), '&') as 'Actual'

Do you think I have to do one giant case when? I have many columns like this, and that would make it unbearable to read.

Thanks!

UPDATE: if I use a case when, then my select looks like this, which seems to work, but is going to be a pain.

select 
    case when (@ColA is not null and @ColA <> '') and
          (@ColB is not null and @ColB <> '') 
        then @ColA + '-' + @ColB
        else COALESCE(NULLIF(COALESCE(@ColA, '') + COALESCE(@ColB, ''), ''), '&')
    end

really hoping someone has some suggestions for improvement!

Solution

  • Could you use a searched case function like this?

    Select Case When isnull(ColA, '') + isnull(ColB, '') == '' Then '&'
            When isnull(ColA, '') <> '' and isnull(ColB, '') <> '' Then ColA + '-' + ColB
            Else isnull(ColA, '') + isnull(ColB, '')
From Table1