CODEWITHSUNDEEP
mysqlfind-in-set
lStoilov
lStoilov

Reputation: 1339

how to check if value from a comma-separated string exists in string MySQL

I have a table with a column named tags. In this column, I have a comma-separated list like air conditioner, tv, fridge.

I want to check if any of the words in the comma-separated column exists in a string like

Is there an air conditioner in the room?

So, the query should return a match as in the column tags, the air conditioner value exists.

I was thinking of using find_in_set, but it will not work in this case as the logic should be reversed.

select * from products where find_in_set('Is there an air conditioner in the room?',tags) <> 0

UPDATE

I could use find_in_set by separating the string into words and then checking against the tags column, but it will not work for a combination like "air conditioner" as it will spit like air, conditioner.

MySQL version 5.7.44

Upvotes: 2

Views: 75

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562348

SELECT * FROM products
CROSS JOIN JSON_TABLE(CONCAT('["', REGEXP_REPLACE(tags, ', *', '","'), '"]'),
  '$[*]' COLUMNS(tag VARCHAR(20) PATH '$')
) AS j
WHERE LOCATE(j.tag, 'Is there an air conditioner in the room?');

Demo: https://dbfiddle.uk/ayNuWEqQ

This cannot be optimized with an index, just like any other substring-matching task.

The above solution with JSON_TABLE() requires MySQL 8.0 or later. MySQL 5.x is now past its end-of-life, but I understand some people have not upgraded. I'll leave this answer here for future folks who read this thread.

Upvotes: 1

Barmar
Barmar

Reputation: 781004

Don't use a comma-separated field in the first place. Normalize your schema by putting the tags into their own table with a foreign key pointing back to the products table.

CREATE TABLE product_tags (
    tag VARCHAR(100),
    product_id INT,
    PRIMARY KEY (tag, product_id),
    FOREIGN KEY (product_id) REFERENCES products (id)
);

Then you can use

SELECT p.*
FROM products AS p
JOIN product_tags AS pt ON p.id = pt.product_id
WHERE LOCATE(pt.tag, 'Is there an air conditioner in the room?')

Upvotes: 2

Bernd Buffen
Bernd Buffen

Reputation: 15057

You can test it like this:

``` 
create table mytags (
  id int(11),
  tags varchar(64)
  );

insert into mytags values(1,"air conditioner, tv, fridge");

SELECT * from mytags;


```


| id | tags |
|---:|:-----|
| 1 | air conditioner, tv, fridge |

``` 
SELECT * FROM mytags WHERE FIND_IN_SET("air conditioner",tags);
```
| id | tags |
|---:|:-----|
| 1 | air conditioner, tv, fridge |

fiddle

Or remove always all spaces. Then you can find it with or without spaces Like:

```

SELECT * FROM mytags WHERE
 FIND_IN_SET(REPLACE("airconditioner"," ",""),REPLACE(tags," ",""));

| id | tags |
|---:|:-----|
| 1 | air conditioner, tv, fridge |

fiddle

Upvotes: 0

Related Questions