How to remove trailing characters with Transact-SQL (t-sql)?

Question

With transact-sql (t-sql) I have a string constant and three string variables I want to Concatenate with a semicolon delimiter between each value. Each of the three string variables are sometimes blank or null, which creates situations where there is one, two, or three semicolon delimiters that I don't want.

I tried Concat_ws() function which is supposed to work according to t-sql websites, but I get error message saying not a recognized function name. I tried two statements with Replace() function where I replace double or triple semicolons with blanks instead. That works but seems crude and won't help me replace just one trailing semicolon. Any ideas?

Sample code that produces too many semicolons when a var is blank:

Concat('Label ', ';', var1, ';', var2, ';', var3) as Result

Concat_WS() is not working for me. Gives error. Not sure what version of t-sql i have, it's part of vendor software here. — Brian S, Aug 20 '19 at 15:01
`Concat_WS` will skip arguments that are `null`, but not empty strings. You could use a `case` expression (or `iif`) to substitute `null` for empty strings. Tip: `select @@Version;` will return the version of SQL Server. — HABO, Aug 20 '19 at 15:01
Please add the results you get from `SELECT @@VERSION` to your question. Seems you're running a version prior to 2017. — Eric Brandt, Aug 20 '19 at 15:07
I will try a case expression or iif. When i ran select @@Version; error said Must delcare the scalar variable @@Version. — Brian S, Aug 20 '19 at 15:08
Thanks for the ideas. The + string concatenation looked simplest to me so I tried that and it worked. https://learn.microsoft.com/en-us/sql/t-sql/language-elements/string-concatenation-transact-sql?view=sql-server-2017 — Brian S, Aug 20 '19 at 16:14

score 1 · Answer 1 · answered Aug 20 '19 at 15:07

Here is an equivalent to concat_ws() in SQL Server:

stuff( coalesce(';' + nullif(var1, ''), '') + coalesce(';' + nullif(var2, ''), '') + coalesce(';' + nullif(var3, ''), ''), 1, 1, '') as Result

You can prepend the 'Label ' to this result. I'm not sure if you really want a semicolon after 'Label ', so it wouldn't be appropriate in concat_ws() either.

score 1 · Answer 2 · answered Aug 20 '19 at 15:34

For this you simply need to change ';', @var# to ';'+@var# inside your CONCAT statement.

DECLARE 
  @var1 VARCHAR(10) = 'abc',
  @var2 VARCHAR(10) = 'xyz',
  @var3 VARCHAR(10) = '123';

SELECT CONCAT('Label ', ';'+@var1, ';'+@var2, ';'+@var3) as Result;

Against a table:

DECLARE 
  @v1 VARCHAR(10) = 'abc',
  @v2 VARCHAR(10) = 'xyz',
  @v3 VARCHAR(10) = '123';

DECLARE @table TABLE (var1 VARCHAR(10),var2 VARCHAR(10),var3 VARCHAR(10));
INSERT @table VALUES (@v1,@v2,NULL),(@v1,NULL,NULL),(@v1,NULL,@v3),(NULL,NULL,NULL)

SELECT CONCAT('Label ', ';'+var1, ';'+var2, ';'+var3) as Result
FROM @table;

Results:

Result
---------------------------------------
Label ;abc;xyz
Label ;abc
Label ;abc;123
Label

To handle blanks you wrap the var# with NULLIF(var#,''). Final Solution:

DECLARE 
  @v1 VARCHAR(10) = 'abc',
  @v2 VARCHAR(10) = 'xyz',
  @v3 VARCHAR(10) = '123';

DECLARE @table TABLE (var1 VARCHAR(10),var2 VARCHAR(10),var3 VARCHAR(10));
INSERT @table VALUES (@v1,@v2,NULL),(@v1,NULL,NULL),(@v1,NULL,@v3),(NULL,NULL,NULL),
                     (@v1,@v2,''),(@v1,'',''),(@v1,'',@v3),(NULL,'',NULL);

SELECT CONCAT('Label ', ';'+NULLIF(var1,''), ';'+NULLIF(var2,''), ';'+NULLIF(var3,'')) as Result
FROM @table;

Results:

Result
---------------------------------------
Label ;abc;xyz
Label ;abc
Label ;abc;123
Label 
Label ;abc;xyz
Label ;abc
Label ;abc;123
Label

score 0 · Accepted Answer · answered Aug 20 '19 at 15:14

Use isnull():

select
  concat('Label ', isnull(';' + var1, ''), isnull(';' + var2, ''), isnull(';' + var3, '')) as Result
from tablename

See the demo.
Results:

> | Result       |
> | :----------- |
> | Label ;a;b;c |
> | Label ;d;e   |
> | Label ;f;g   |
> | Label ;h;i   |
> | Label ;j     |
> | Label ;k     |
> | Label ;l     |
> | Label        |

score 0 · Answer 4 · answered Aug 20 '19 at 15:59

    DECLARE @VAR1 VARCHAR(10)
    DECLARE @VAR2 VARCHAR(10)
    DECLAR  @VAR3 VARCHAR(10)
    DECLARE @VAR11 VARCHAR(10)
    DECLARE @VAR22 VARCHAR(10)
    DECLAR  @VAR33 VARCHAR(10)

    SET @VAR1 = …
    SET @VAR2 = … 
    SET @VAR3 = … 

IF @VAR1 IS NOT NULL
BEGIN
    SET @VAR11 = CONCAT(@VAR1,';')
ELSE
SET @VAR11 = ''

IF @VAR2 IS NOT NULL 
BEGIN
    SET @VAR22 = CONCAT(@VAR2,';')
ELSE
SET @VAR22 = ''

IF @VAR3 IS NOT NULL
BEGIN
    SET @VAR33 = CONCAT(@VAR2,';')
ELSE 
SET @VAR33 = ''

    Concat('Label ', ';', @var11, @var22, @var33) as Result

How to remove trailing characters with Transact-SQL (t-sql)?

4 Answers4