0

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
  • 1
    No need to guess that CASE stops after the first match. This is clearly documented. Specify separate CASE expression for each rule.
    – Dan Guzman
    Apr 19, 2020 at 13:37
  • 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 :). Apr 19, 2020 at 14:00

1 Answer 1

4

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]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.