Mohammad February 2016

MYSQL: search IN string as an array of integers

I have a field named authors which is a varchar storing the IDs of the authors (I know its wrong to save the IDs as a varchar !!) I want to search in this field for an int, example:

SELECT * FROM table WHERE 3 IN CONVERT(SUBSTRING_INDEX(`authorid`,',',-1),UNSIGNED INTEGER)

Where authorid value is '3,4,5'.

Is it possible, how I can do it?

Answers


PravinS February 2016

Use FIND_IN_SET() MySQL function

Try this

SELECT FIND_IN_SET('3',authorid);

Hope this will work for you.


Kickstart February 2016

If presented with this problem the solution by PravinS is probably best in the short term.

It is possible to split the numbers in SQL but not worthwhile unless you just want to prove a point! For example, coping with up to 100 comma separated values:-

SELECT a.*
FROM table a
CROSS JOIN
(SELECT 0 AS acnt UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
CROSS JOIN
(SELECT 0 AS acnt UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
WHERE LENGTH(authorid) - LENGTH(REPLACE(authorid, ',', '')) >= (tens.acnt * 10 + units.acnt)
AND SUBSTRING_INDEX(SUBSTRING_INDEX(authorid, ',', tens.acnt * 10 + units.acnt + 1), ',', -1) = 3

Post Status

Asked in February 2016
Viewed 3,653 times
Voted 7
Answered 2 times

Search




Leave an answer