IF Else string check condition in sql server

Question

I have a records like below :

Name                Count
123456M2.txt        NULL
123456M2.txt        15
123456M.txt         NULL

First record shoud show me NULL, as there is a number after "M" letter in name and Count < 0
Second record shoud show me 1, and there is a number after "M" letter in name and Count > 0
Third record should show me "some number from another field" as, there is no number after "M" letter in name.

Can you guide me on how to write this query.

I am able to do CASE - WHEN query, but not able to proceed with IF ELSE condition of letter check

CASE
    WHEN CHARINDEX('M', [FILE_NAME]) > 0 THEN 'COUNT'

You appear to have answered your own question - write it with a Case...When structure. If you want further assistance, I would suggest you edit your question to include sample data — Brian
– Brian, Commented Oct 18, 2018 at 15:00
I will have to write a check if any number exists after the letter "M" then do this. — A Developer
– A Developer, Commented Oct 18, 2018 at 15:02
For the first record (sic) the count is not < 0, it is NULL. — MJH
– MJH, Commented Oct 18, 2018 at 15:03

Tab Alleman · Accepted Answer · 2018-10-18 15:02:13Z

4

You need to use multiple conditions for each WHEN in the CASE:

(psuedocode)

WHEN {Name contains 'M'} AND {character after 'M' is a number} AND {Count is NULL or < 0} THEN NULL
WHEN {Name contains 'M'} AND {character after 'M' is a number} AND {Count is NULL or < 0} THEN 1
etc...

there is no need to try to add IF..ELSE logic anywhere in your query.

answered Oct 18, 2018 at 15:02

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

A Developer Over a year ago

I am not sure on how to write the piece - character after 'M' is a number

Tab Alleman Over a year ago

Get the CHARINDEX of 'M' and add 1. The character at that position is the character after 'M'.

Gordon Linoff · Accepted Answer · 2018-10-18 15:11:21Z

2

I would phrase this as:

select . . .,
       (case when name like '%M[0-9]%' and count > 0 then 1
             when name like '%M[0-9]%' then 0
             else "some number from another field"
        end)

A case expression evaluates the conditions in order, stopping at the first one that evaluates to "true".

Your description is a bit unclear, because NULL is not < 0.

answered Oct 18, 2018 at 15:11

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Ilyes · Accepted Answer · 2018-10-18 15:19:08Z

Try the following

DECLARE @TBL TABLE (Name VARCHAR(25), [Count] INT);

INSERT INTO @TBL VALUES
('123456M2.txt',        NULL),
('123456M2.txt',        15),
('123456M.txt',         NULL);

SELECT *,
      CASE WHEN Name LIKE '%M[0-9]%' AND ([Count] IS NULL OR [Count] < 0) THEN --Check for NULL too cause NULL is not < 0
                NULL
           WHEN Name LIKE '%M[0-9]%' AND [Count] > 0 THEN
                1
           WHEN Name LIKE '%M.%' THEN
                555 --Other value from other column
                END AS Results
FROM @TBL;

Results:

+--------------+-------+---------+
|     Name     | Count | Results |
+--------------+-------+---------+
| 123456M2.txt |       |         |
| 123456M2.txt |    15 |       1 |
| 123456M.txt  |       |     555 |
+--------------+-------+---------+

Collectives™ on Stack Overflow

IF Else string check condition in sql server

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related