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)],

• Dan Guzman: No need to guess that CASE stops after the first match. This is clearly documented. Specify separate CASE expression for each rule.
• bionicsamir: I have such 65 WHEN statements. If I specify 65 separate CASE, then I will have 65 separate columns. Is there any way I can have the results in one column? I am not an expert in SQL but try to get to it :).

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]