SQL IF Statement using multiple ISNULL

user_10788269 0
Viewed 291

I have the below code that is returning the order that zip codes are visited. I am able to return the zip codes correctly, but in order to make the data more user friendly I have added a dash(-) between the zip codes.

The problem comes from the fact that I cannot figure out how to eliminate the dashes for rows that only have 2 or 3 zip codes.

enter image description here

SELECT 
    [Qry_Zip Stop Sequence].[Load ID], 
    [1] AS [Stop 1], [2] AS [Stop 2], [3] AS [Stop 3], 
    [4] AS [Stop 4], 
    TMS_Load.[Shipped Date/Time], 
    CONCAT(ISNULL([1], ''), '-', ISNULL([2], ''), '-', ISNULL([3], ''), '-', ISNULL([4], '')) AS [Zip to Zip w Stops]
FROM 
    (SELECT 
         [Load ID], [Sequence], [Stop Zip]
     FROM 
         TMS_Load_Stops) ls
PIVOT 
    (MIN([Stop Zip])
        FOR [Sequence] IN ([1], [2], [3], [4])) AS [Qry_Zip Stop Sequence]
INNER JOIN 
    [TMS_Load] ON [TMS_Load].[Load ID] = [Qry_Zip Stop Sequence].[Load ID];

I would like the results to show only show the dashes between valid zip codes.

78052-45050-45201 or
73350-45220 or
84009-48009-14452 or
36521-38222-87745-95123 or
73368 or
12789-35789
concat sql-server-2012 tsql sql-server sql
3 Answers

SQL Server 2017 supports CONCAT_WS which is designed for such scenario:

CONCAT_WS ignores null values during concatenation, and does not add the separator between null values. Therefore, CONCAT_WS can cleanly handle concatenation of strings that might have "blank" values - for example, a second address field

SELECT *, CONCAT_WS('-', Stop1, Stop2, Stop3, Stop4) AS r
FROM tab

db<>fiddle demo

user_50708790

Prepend every value with a dash and remove the first dash in the resulting string -- that isn't necessarily the one from the first value -- using stuff().

stuff(concat('-' + [1],
             '-' + [2],
             '-' + [3],
             '-' + [4]),
      1,
      1,
      '')

Note: I deliberately mixed + and concat() for string concatenation here. + results in NULL when a value is NULL but concat() treats NULLs like empty strings. That way we don't need to use a lot of coalesce()s or isnull()s etc..

user_96614240

The following code will insert separators only for non-NULL values. It is assumed that columns are populated from left to right.

declare @Stops as Table ( Stop1 Char(5), Stop2 Char(5), Stop3 Char(5), Stop4 Char(5) );
insert into @Stops ( Stop1, Stop2, Stop3, Stop4 ) values
  ( '00001', null, null, null ),
  ( '00001', '00002', null, null ),
  ( '00001', '00002', '00003', null ),
  ( '00001', '00002', '00003', '00004' );


select Coalesce( Stop1, '' ) + Coalesce( '>' + Stop2, '' ) + Coalesce( '>' + Stop3, '' ) +
  Coalesce( '>' + Stop4, '' )
  from @Stops;

Aside: A separator other than dash may be less confusing in lands where ZIP+4 codes are used.

user_925460
Related