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?
Use FIND_IN_SET() MySQL function
Try this
SELECT FIND_IN_SET('3',authorid);
Hope this will work for you.
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
