SQL Select rows where column X contains number n from the string list e.g. 1,5,6

Hello everyone. The last days I tried write query which will be select all rows where cat_ids column have number 5(for example) from below table.

+----+---------+
| id | cat_ids |
+----+---------+
| 1  | 1,5,6   |
+----+---------+
| 2  | 5,9,7   |
+----+---------+

Our goal: Try get all rows where cat_ids have 5 value.

So if we try to get first number from field cat_ids then we use this mysql command for it SELECT id, cat_ids, SUBSTRING_INDEX(`cat_ids`, ‘,’, 1) as `cat_id` FROM `table_183_t` HAVING `cat_id`=1 After the code display this result.

+----+---------+--------+
| id | cat_ids | cat_id |
+----+---------+--------+
| 1  | 1,5,6   | 1      |
+----+---------+--------+

But as you saw it’s command not solve our needs completely because if 1 value been next to 5 the query didn’t find anything. Query request only see first letter in field. So if we try get row when cat_ids have value 9 it returns nothing. MySql not have any function which can check field like we want and for it we need create mysql new function byself .

We will call it CHECK_IN_FIELD()

DELIMITER $$
CREATE FUNCTION SPLIT_STR(
 x VARCHAR(255),
 delim VARCHAR(12),
 pos INT
)
RETURNS VARCHAR(255) DETERMINISTIC
BEGIN 
 RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
 LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
 delim, ‘’);
END$$
CREATE FUNCTION CHECK_IN_FIELD(cat_ids text, check_number integer) RETURNS INTEGER(1)
 DETERMINISTIC
BEGIN
 DECLARE isExist integer(1);
 DECLARE length integer(2);
 DECLARE current_number integer(2);
 
 SET length = LENGTH(REPLACE(cat_ids, “,”, “”));
 SET isExist = 0;
 
 WHILE length > 0 DO
 SET current_number = SPLIT_STR(cat_ids, ‘,’, length);
IF current_number = check_number THEN SET isExist = 1; END IF;
 
 SET length = length — 1;
 END WHILE;
 
 RETURN (isExist);
END

You can see I also add one more function by name SPLIT_STR() . It’s also additional function for our logic which we use in CHECK_IN_FIELD()

Now run below command in your sql panel

SELECT * FROM `table_183_t` WHERE CHECK_IN_FIELD(`cat_ids`, 5)

SQL

After we will see these data

+----+---------+
| id | cat_ids |
+----+---------+
| 1  | 1,5,6   |
+----+---------+
| 2  | 5,9,7   |
+----+---------+

💛 Yes we found method which can save our time!

If you want use it in your server side( with PHP, Python, NodeJS and etc) you can see how execute mysql “create function” statement with PHP for example. Like example for PHP look it https://stackoverflow.com/questions/14569528/execute-mysql-create-function-statement-with-php

For remove functions run it’s query

DROP FUNCTION SPLIT_STR;
DROP FUNCTION CHECK_IN_FIELD;

If this article was helpful, please clap! And of course I will be glad answer on yours questions.

Also you can see my other article by name Nodejs Routing without using Express JS