Sql Statement to display multiple true statement

Transact-SQL
1

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

2

Welcome, please provide sample data as follows

Create table #sample(specialdata varchar(max))

Insert into #sample
Select 'idjrjdjdkdjdjdkjdjrjr' union
Select 'jdjdj4948brirjrj'```
3 
SELECT

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