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 :
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]