MYSQL find INT in string

Question

I have a column MEDIUMTEXT that contains values that come from a goup_concat, in the form of INT,INT,INT . We can call it Concatenated_IDs

The length of the string can be of 1 int or more.

I need to break it down into original values somehow to be able to do something such as

SELECT
  table_country.name
FROM
  table_country 
WHERE
  table_country.country_id IN (
   SELECT
     Concatenated_IDs
   FROM
     table_targeted_countries 
   WHERE
     table_targeted_countries.email LIKE "%gmail.com")

and get the country names that users registered with a gmail address target.

I have considered exploding the mediumtext into INT, creating one row for each int, sort of like a reverse concat, but I am guessing it would take a large procedure

edit:reformulated question name

Jason McCreary · Accepted Answer · 2013-05-03 14:31:27Z

4

You should probably normalize that table, so those concated ids are stored in a separate table, one id per record. But in the mean time, you can use mysql's find_in_set() function:

SELECT ...
WHERE FIND_IN_SET(table_country.country_id, Concatenated_IDs) > 0

Relevant docs: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

edited May 3, 2013 at 14:31

Jason McCreary

73.3k23 gold badges140 silver badges177 bronze badges

answered May 3, 2013 at 14:29

Marc B

362k44 gold badges433 silver badges508 bronze badges

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

2 Comments

AdrianBR Over a year ago

Thank you, find_in_set solved my problem. table will be normalised some time in the future, it's in a long queue

user645280 Over a year ago

LOCATE() would also work if you prefix and suffix all strings with ','. e.g. LOCATE(CONCAT(',', id_to_find, ','), CONCAT(',',Concatenated_IDs,',')). FIND_IN_SET() is made for this however.

Collectives™ on Stack Overflow

MYSQL find INT in string

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related