CODEWITHSUNDEEP
mysql
Kalenji
Kalenji

Reputation: 407

MySQL - multiple case when for varchars

I am trying to use CASE WHEN to count when specific text is withing the varchar column. I have been trying to crack it but I have an issue when second, third CASE WHEN is added. The code I use and works is:

SELECT *,
    CASE 
    WHEN (Orders.Some_text LIKE "%test%" AND Orders.Some_text NOT LIKE "%found%") THEN 1

    ELSE 0 END AS Test
    FROM Orders;

Now I add the second CASE WHEN:

SELECT *,
    CASE 
    WHEN (Orders.Some_text LIKE "%test%" AND Orders.Some_text NOT LIKE "%found%") THEN 1
    WHEN (Orders.Some_text LIKE "%test%" AND Orders.Some_text NOT LIKE "%missing%") THEN 1
    ELSE 0 END AS Test
    FROM Orders;

And it produces errors in the Test column.

Results I want are just simple 1 when word test is found and it is without found, missing or/and during.

+------+--------------+------------+
| id   | Some_text          | Test |
+------+--------------+------------+
|    1 | test               |    1 |
|    2 | test found         |    0 |
|    3 | found test         |    0 |
|    4 | test missing       |    0 |
|    5 | missing test       |    0 |
|    6 | test during        |    0 |
|    7 | during test found  |    0 |
|    8 | abc                |    0 |
+------+--------------+------------+

The code to reproduce my dataset:

CREATE TABLE Orders
(

id INT,
Some_text char(255));




insert into Orders values (1,   "test");
insert into Orders values (2,   "test found");
insert into Orders values (3,   "found test");
insert into Orders values (4,   "test missing");
insert into Orders values (5,   "miss   ing test");
insert into Orders values (6,   "test during");
insert into Orders values (7,   "during test found");
insert into Orders values (8,   "abc");

Upvotes: 0

Views: 54

Answers (1)

Samir Selia
Samir Selia

Reputation: 7065

It seems you need to check if both the words found and missing aren't there in the field, then set the value as 1.

Combine the statements like below and it should return expected output.

SELECT *,
CASE 
WHEN (Orders.Some_text LIKE "%test%" AND
      Orders.Some_text NOT LIKE "%found%" AND
      Orders.Some_text NOT LIKE "%missing%") THEN 1
ELSE 0 END AS Test
FROM Orders;

Output

+------+--------------+------------+
| id   | Some_text          | Test |
+------+--------------+------------+
|    1 | test               |    1 |
|    2 | test found         |    0 |
|    3 | found test         |    0 |
|    4 | test missing       |    0 |
|    5 | missing test       |    0 |
|    6 | test during        |    1 |
|    7 | during test found  |    0 |
|    8 | abc                |    0 |

Upvotes: 1

Related Questions