How to search specific value in semicolon separated column in mysql

Asked Dec 08 '20 at 11:56

Active Dec 08 '20 at 14:59

Viewed 160 times

I have values like (P12, I2) and I want to search them in semicolon-separated MySQL column name custom_column, which has values like P% or P2 or P2;I2 or P%;I2. (Yes, it should work with wildcard too.)

I tried as below in where clause.

WHERE 'P2' LIKE CONTACT(table2.custom_column, '%')

But it is not working.

How can I solve this?

edited Dec 08 '20 at 14:59

asked Dec 08 '20 at 11:56

Rohit Kundale

Read ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) and fix your schema. – sticky bit Dec 08 '20 at 12:16
@stickybit I am not designing the table. I am just using the existing table from Magento 2 cloud. – Rohit Kundale Dec 08 '20 at 12:18
Replace `;` with `,` and use FIND_IN_SET(). – Akina Dec 08 '20 at 12:20
@Akina I tried this but after that it is not working with Wildcard search. – Rohit Kundale Dec 08 '20 at 12:31
Wildcard search not needed. `WHERE FIND_IN_SET('P12', REPLACE(table2.custom_column, ';', ',')` – Akina Dec 08 '20 at 12:32
@Akina Then how can it work if custom_column has the value `P%` or `P%;I2`? – Rohit Kundale Dec 08 '20 at 13:21
Your column stores PATTERNS in CSV format??? you're in a cesspool... – Akina Dec 08 '20 at 13:24
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/225670/discussion-between-rohit-kundale-and-akina). – Rohit Kundale Dec 08 '20 at 13:37
@Akina Actually, the search should return value if table2.custom_column has any value as below `P%` or `P2` or `P2;I2` or `P%;I2`, Any suggestion? – Rohit Kundale Dec 08 '20 at 14:09

How to search specific value in semicolon separated column in mysql

0 Answers0