SQL FIND_IN_SET command to search strings within a set of string
Here search_string is used to search on on set of strings separated by coma.
0 : if no match is found
number : position of the matching set
null : If search is applied on a null set
Example of FIND_IN_SET on string
No matching record so Output 0
No records found
Outptu is here ( one record found )
FIND_IN_SET & LOCATEWe have already seen FIND_IN_SET against string. We will find the difference between FIND_IN_SET and LOCATE query.
No matching record so Output is 0
Output is 9
Here keyword is not matched against each set of string, it is matching as a total string so the position of matching string ry is located at 9th position from left. Here position of (, ) coma is also considered as one char for calculating the postion of matching string.
One matching record we will get, Output is here
Here we get one record with id = 2 , now let us try LOCATE query using student table.
We will get 14 matching records as all records having id equal to 2, 12, 20,21, ..... 32,42 will be retured. The matching is done by using the ID field as string.
Difference with INWhile using IN Query , We search for a set of key words on a column like this
While using SQL_FIND_IN we use one keyword on a column like this.
We get one matching record , output is here
The output of this query is here.
The above command will return empty results set. Here the string 'john' is matched with all data in name column of student table.
Download sql dump of this student table