Mysql Question posted in
The official MySQL documentation can be found here.

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.

Tags:

3

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0 
    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.

    Login or Signup to reply.
  3. 0

    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?')
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search