CODEWITHSUNDEEP
mysql
SP Singh
SP Singh

Reputation: 438

comma separated string comparison in mysql query

I want to search countries from a table as given below:

+-----+-------------+
| id  | country     
+-----+-------------+
| 1   | US          
+-----+-------------+
| 2   | US,IN,UK
| 3   | US,NZ
| 4   | AUS

How I perform sql query to get all records containing a country = 'US' with additional comma separated countries?
Thanks

Upvotes: 1

Views: 1906

Answers (6)

Sajan Sharma
Sajan Sharma

Reputation: 109

You can use FIND_IN_SET function, for example -

 SELECT * FROM table WHERE FIND_IN_SET('US', country) ;

Upvotes: 2

Naveed S
Naveed S

Reputation: 5236

You may use LIKE or RLIKE for this.

SELECT * FROM table_name WHERE country RLIKE '(.+?,)*?US(,.+?)*'

or

SELECT * FROM table_name WHERE country LIKE '%US%'

First query avoids retrieval of ones with country having US as substring like RUS

SELECT * FROM table_name WHERE country RLIKE '(.+,)(.+,)*?US(,.+)*|(.+,)*?US(,.+)(,.+)*'

selects only those with at least another entry along with US.

Upvotes: 1

Devart
Devart

Reputation: 121902

You can use FIND_IN_SET function, for example -

SELECT * FROM table
WHERE FIND_IN_SET('US', country) ;

Upvotes: 2

Madhav
Madhav

Reputation: 2415

Try the below code.

SELECT ID,COUNTRY FROM TABLE_NAME
WHERE COUNTRY LIKE '%US%;

Upvotes: 1

John Woo
John Woo

Reputation: 263693

SELECT  *
FROM    tableName
WHERE   FIND_IN_SET('US', country) > 0

Upvotes: 1

fthiella
fthiella

Reputation: 49049

SELECT id, country
FROM yourtable
WHERE FIND_IN_SET('US', country)

or

SELECT id, country
FROM yourtable
WHERE CONCAT(',', country, ',') LIKE '%,US,%'

Please see fiddle here.

Upvotes: 1

Related Questions