colleges table bellow

CourseID = 1;

q = "SELECT * FROM colleges WHERE Courses = CourseID";

that ID is an (int) value but I have some comma-separated values in the Courses column.

is there any way in SQL to solve this problem

CodePudding user response：

I share @jarlh's view. This is a very bad idea.

Having said that, you can still do something like this:

SELECT ... WHERE CONCAT(',',courses,',') LIKE CONCAT('%,', course, ',%')

Again, not a great way to store your data.

Just be careful of extra whitespace in the data and adjust as needed.

See @Ergest's answer for a nice MySQL specific approach.

CodePudding user response：

You should fix your table design and never store data as comma separated.

You could use FIND_IN_SET

SELECT * FROM colleges where FIND_IN_SET(1, Courses);

Demo