CODEWITHSUNDEEP
mysql
PSmy
PSmy

Reputation: 21

MySQL query String has comma only once

I have got a table in one of the columns I got data separated by comma, I've been trying to figure out how I can make a query with MySQL that checks in this column that I got a name that has comma only once:

table

SELECT Name, num FROM biology WHERE Name = 'Yossi' AND contains (', ')=1 ;

So I would get only

table2

Thanks

Upvotes: 0

Views: 176

Answers (1)

Ike Walker
Ike Walker

Reputation: 65537

You an use FIND_IN_SET() to find rows where the comma separated list contains a specific word.

There is no elegant way to count the rows with exactly two elements in the comma separated list, but one way to accomplish this is to compare the length of the string with commas to the length of the string with the commas removed. If removing all commas shortens the length by 1 character that means there is exactly one comma.

Something like this should work for you:

select * 
from your_table 
where find_in_set('Yossi',name) 
and char_length(name) = (char_length(replace(name,',','')) + 1)

Upvotes: 1

Related Questions