Sql Statement to display multiple true statement

Question

Sql Statement to display multiple true statement

bionicsamir

SSC Enthusiast

Points: 183
More actions
April 19, 2020 at 2:06 pm

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Here is one way. I switched things around a bit and used a temp table to show a working query:

DROP TABLE IF EXISTS #tmp;

CREATE TABLE #tmp
(
    DSC1 VARCHAR(1000) NOT NULL
);

SELECT [Special Character] = CONCAT(t2.Char1, t2.Char2, t2.Char3)
FROM #tmp t1
    CROSS APPLY
(
    SELECT Char1 = IIF((LEN(DSC1) - CHARINDEX(CHAR(1), DSC1)) <> LEN(DSC1), '[DSC1 - NUL (null)], ', '')
          ,Char2 = IIF((LEN(t1.DSC1) - CHARINDEX(CHAR(2), t1.DSC1)) <> LEN(t1.DSC1)
                       ,'[DSC1 - SOH (start of heading)], '
                       ,'')
          ,Char3 = IIF((LEN(t1.DSC1) - CHARINDEX(CHAR(3), t1.DSC1)) <> LEN(t1.DSC1)
                       ,'[DSC1 - STX (start of text)], '
                       ,'')
) t2;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 2

bionicsamir wrote:

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

First of all, I'm not sure what your source of information is but all of your CHAR() codes are incorrect and off by one.

CHAR(0) is the NUL character.

CHAR(1) is the SOH character.

CHAR(2) is the STX character.

CHAR(3) is the ETX character.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bionicsamir SSC Enthusiast Points: 183 More actions · Answer 3

Thanks Phil. I actually went with the Case statement for each of them and enclosed them with the concat.

Jeff, yes I noticed that later and had corrected.

This reply was modified 4 years, 7 months ago by bionicsamir.