Title: mysql like query with check of values in comma separated data
Tags: mysql
Question: I have a table with data like this

```+-------+---------------------+------------------------------------------------+--------------------+
|  id   |        name         |                   movieyears                   | length(movieyears) |
+-------+---------------------+------------------------------------------------+--------------------+
| 85530 | Nargis Fakhri       | [2011,2013,2014]                               |                 16 |
| 26683 | Nawazuddin Siddiqui | [1999,2006,2007,2009,2010,2012,2013,2014,2015] |                 46 |
| 14508 | Aditi Rao Hydari    | [2009,2011,2012,2013,2014]                     |                 26 |
+-------+---------------------+------------------------------------------------+--------------------+
```

Challenge is to find the rows for which the actors were only active in past 4 years and not in any other years, so essentially a good query should return only the first row of 'Nargis Fakhri' and not of any other actor, I know about Find_In_set but that's to find if actor existed for one particular year.

Comment: Don't store multiple values in a single column.

Comment: 1. See normalization. That's all.

Comment: I wish i could, I am out of option here, and Yes i do know normalization, I am just not in state to do so.

Here is the accepted answer: The solution would be quite as ugly as the table design...

```select
  id, name
from
  TheTable
where
  movieyears <&gt; '[]' and
  replace(replace(replace(replace(replace(movieyears, '2011', ''), '2012', ''), '2013', ''), '2014', ''), ',', '') = '[]'
```

Comment for this answer: an ugly code needs an ugly mate, couldn't agree more :)

Here is another answer: As a note, your years seem to be ordered.  If this is the case:

```select t.*
from tablewithdata t
where substring_index(movieyears, ',', 1) &gt;= '2011';
```


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 2, 'CommentCount': 3, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2014:00:11/17/14', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<mysql>', 'Title': 'mysql like query with check of values in comma separated data', 'ViewCount': 72, 'id': 26971593, 'user_AboutMe': '<p>Sr. Software Engineer, CSE (Microsoft)</p>\n\n<p>Hi,\n    I am Brij, and I am a Senior Software Engineer in Commercial Software Engineering at Microsoft.</p>\n', 'user_AccountId': 1301953.0, 'user_DisplayName': 'Brij Raj Singh - MSFT', 'user_Reputation': 4743.0, 'user_Views': 811.0, 'user_id': 1251674.0}}