0

I'm having a table called users, it has a column of datatype enum. I would like to search in enum in the SELECT query

Table Structure:

CREATE TABLE `users` (
  `id` binary(16) NOT NULL PRIMARY KEY,
  `name` varchar(100) NOT NULL,
  `type` enum('A','B','C','D','E','F') NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Sample Data:

INSERT INTO `cm_contact` (`id`, `name`, `type`)  VALUES
(0x0b0b10bf94e511e9a32efa163e49d265, 'John', 'A,C,E'),
(0x0fd283a494b111e9a32efa163e49d265, 'Ellen', 'E,F'),
(0x25c77b6294e111e9a32efa163e49d265, 'Emma', 'B,C,D');

I need to filter the record, that who are all under the type B and C, that means it will return the user John and Emma

I tried the following approach but I failed

SET @search='B,C';

SELCT * FROM users WHERE type IN @search

Kindly assist me how to achieve this.

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
B.Balamanigandan
  • 4,713
  • 11
  • 68
  • 130
  • Please stop storing delimiter separated values; and instead normalize the DB. 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) – Madhur Bhaiya Sep 11 '19 at 06:19
  • @MadhurBhaiya - The FIND_IN_SET() function returns the position of a string within a list of strings. but in my case both are the sets (i.e., list), I need to find any intersection element is there or not. – B.Balamanigandan Sep 11 '19 at 06:22
  • ^^ The accepted answer in the duplicate question link shared above is the answer to your problem. But you should really normalize your data, if you really want to fix things structurally and build things for performance. – Madhur Bhaiya Sep 11 '19 at 06:24
  • @MadhurBhaiya - Its solved my problem. Thanks. – B.Balamanigandan Sep 11 '19 at 06:30

0 Answers0