Sql Statement to display multiple true statement

Question

I have a sql statement that checks for certain special characters in a string and returns that.

SELECT

CASE
WHEN (LEN(DSC1) - CHARINDEX(char(1), DSC1)) <> LEN(DSC1) THEN '[DSC1 - NUL (null)], '
WHEN (LEN(DSC1) - CHARINDEX(char(2), DSC1)) <> LEN(DSC1) THEN '[DSC1 - SOH (start of heading)], '
WHEN (LEN(DSC1) - CHARINDEX(char(3), DSC1)) <> LEN(DSC1) THEN '[DSC1 - STX (start of text)], '

END  [Special Character]

The issue with below is that if the string has more than one special character, it just lists the first one and not the other as I guess the case statement breaks as soon as it finds the first match.

How do write that it lists all that it finds. e.g. if the string under DSC1 has both char(1) and char(2), then it will return

Special Character

---

[DSC1 - NUL (null)], [DSC1 - SOH (start of heading)],

Answer 1

You'll need separate CASE expressions since evaluation stops after the first condition is satisfied.

You could use CONCAT to concatenate the values of all expression results into a single column. If the result of a CASE is NULL (WHEN predicate is false or unknown), CONCAT will return an empty string for the value.

SELECT
    CONCAT(
        CASE WHEN (LEN(DSC1) - CHARINDEX(char(1), DSC1)) <> LEN(DSC1) THEN '[DSC1 - NUL (null)], ' END,
        CASE WHEN (LEN(DSC1) - CHARINDEX(char(2), DSC1)) <> LEN(DSC1) THEN '[DSC1 - SOH (start of heading)], ' END,
        CASE WHEN (LEN(DSC1) - CHARINDEX(char(3), DSC1)) <> LEN(DSC1) THEN '[DSC1 - STX (start of text)], ' END
        ) AS [Special Character]