0

How to search within a list of comma separated strings in single column in mysql

Spread the love

If You have a field arr_val (varchar(50)) in a your table tst_tbl that contains a comma delimited string such as PHP,MySql,Ajax,JQuery,PHP 5.

When running the query select * from arr_val where arr_val like ‘%PHP%’ to get all the only PHP Results ( not PHP 5) , But you will also get the result PHP 5.

Now the Question is “How should we rewrite the query so that is selects ONLY the PHP and not all values containing PHP ?”

So Guys we are going to use a MySql Function FIND_IN_SET() to get except matching result from comma separated value

How to search within a list of comma separated strings in single column in mysql

 

FIND_IN_SET() function :

MySQL FIND_IN_SET() returns the position of a string if it is present (as a substring) within a list of strings. The string list itself is a string contains substrings separated by ‘,’ (comma) character.

This function returns 0 when search string does not exist in the string list and returns NULL if either of the arguments is NULL.

 

Syntax :

FIND_IN_SET (search string, string list)

Argument :

NameDescription
search stringA string which is to be looked for in following a list of arguments
string listList of strings to be searched if they contain the search string

MySql Query :

This Above query Will return this Output :

photo2

The above mysql query   use "," as separator and divide all the column value.After That it will Match exact string in all columns and return the matched result.

Hope it will Help You and please like my facebook page :

and make me proud.By the way if you face any problem then let me know in comment section below

Leave a Reply

Your email address will not be published. Required fields are marked *