CODEWITHSUNDEEP
sqlsql-server
A Developer
A Developer

Reputation: 1031

IF Else string check condition in sql server

I have a records like below : 

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

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'

Upvotes: 0

Views: 3571

Answers (3)

Ilyes
Ilyes

Reputation: 14928

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 |
+--------------+-------+---------+

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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.

Upvotes: 2

Tab Alleman
Tab Alleman

Reputation: 31775

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.

Upvotes: 4

Related Questions