CODEWITHSUNDEEP
mysql
Brij Raj Singh - MSFT
Brij Raj Singh - MSFT

Reputation: 5113

mysql like query with check of values in comma separated data

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.

Upvotes: 0

Views: 74

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

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

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

Upvotes: 0

Guffa
Guffa

Reputation: 700222

The solution would be quite as ugly as the table design...

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

Upvotes: 1

Related Questions