comma separated string comparison in mysql query

Question

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

@Martin Bean, Thank you so much for your advice. I will keep it in my mind wherever I will post any question — SP Singh
– SP Singh, Commented Feb 18, 2013 at 11:36

Hamlet Hakobyan · Accepted Answer · 2013-02-18 10:34:48Z

2

You can use FIND_IN_SET function, for example -

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

edited Feb 18, 2013 at 10:34

Hamlet Hakobyan

33.5k6 gold badges55 silver badges70 bronze badges

answered Feb 18, 2013 at 10:29

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sanjeev shetty Over a year ago

in inner join conditions SELECT * FROM table1 t1 INNER JOIN table2 t2 ON FIND_IN_SET(t2.id, t1.column2)

Sajan Sharma · Accepted Answer · 2013-03-01 09:41:47Z

2

You can use FIND_IN_SET function, for example -

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

answered Mar 1, 2013 at 9:41

Sajan Sharma

1095 bronze badges

Comments

fthiella · Accepted Answer · 2013-02-18 10:29:38Z

1

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.

answered Feb 18, 2013 at 10:29

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

Comments

John Woo · Accepted Answer · 2013-02-18 10:29:42Z

1

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

FIND_IN_SET

answered Feb 18, 2013 at 10:29

John Woo

265k70 gold badges509 silver badges500 bronze badges

Comments

Madhav · Accepted Answer · 2013-02-18 10:30:28Z

1

Try the below code.

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

answered Feb 18, 2013 at 10:30

Madhav

2,4153 gold badges19 silver badges16 bronze badges

Comments

Naveed S · Accepted Answer · 2013-02-18 10:46:34Z

1

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.

edited Feb 18, 2013 at 10:46

answered Feb 18, 2013 at 10:29

Naveed S

5,3064 gold badges37 silver badges52 bronze badges

Collectives™ on Stack Overflow

comma separated string comparison in mysql query

6 Answers 6

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related