If I have a table called 'invitation' like this:
| invitation_id | sender | recipient | | 1 | Josh | James, Hendry, Max | | 2 | Steave | James, Gerrard, Ramon | | 3 | Steave | Gerrard, Max |
How can I show invitation data that I received, if my name is "James"?
When using like %james% you may get incorrect results, if similar name occurs. So using find_in_set can solve your problem.
select * from invitation where find_in_set('james',recipient) <> 0
You can also use like in this way.
select * from invitation where ',' + recipient + ',' like '%,james,%'
Use LIKE:
SELECT *
FROM invitation
WHERE recipient LIKE '%james%'
Storing the data (recipient) in comma separated is really not a good practice.
But you still can use find_in_set
You should iterate with you all the values of recipient and do like this
SELECT FIND_IN_SET('James','James,Hendry,Max')
SELECT FIND_IN_SET('James','James, Gerrard, Ramon')
Which will return 0 or 1
Use of LIKE will be good for searching purpose.
But We should use the function FIND_IN_SET() for this type of problem, Where data in table exist as comma separated format.
