How to execute MySQL query with a range of years in one field

Question

I am working with a table that uses year ranges for some of the data that I need to select a year record that is between these ranges.

| id | Make      | Model      | Year        |
|----------------|------------|------------ |
| 1  | Chevrolet | Camaro     | 2008        |
| 2  | Chevrolet | Camaro     | 2009 - 2014 |
| 3  | Dodge     | Avenger    | 2010 - 2015 |
| 4  | Dodge     | Challenger | 2008 - 2016 |   
| 5  | Ford      | Escape     | 2013        |
| 6  | Ford      | Mustang    | 2004 - 2012 |
| 7  | Ford      | Mustang    | 2015        |

For example, I want to be able to select all cars from 2012 .

This should return: 2 , 3 , 4, and 6 , as shown in the example table below.

Answer 1

Use LEFTand RIGHTto define ranges.

SELECT *
FROM yourtable
WHERE (LEFT(Year,4) <= '2012' AND RIGHT(Year,4) >= '2012')

OUTPUT:

id  Make        Model       Year
2   Chevrolet   Camaro      2009 - 2014
3   Dodge       Avenger     2010 - 2015
4   Dodge       Challenger  2008 - 2016
6   Ford        Mustang     2004 - 2012

SQL Fiddle:

Answer 2

SELECT t.*
FROM   t WHERE  LEFT(Year,4) <= '2012' AND RIGHT(Year,4) >= '2012'

Answer 3

, - : , , , .

select *
from table
where (Year = '2012' and substring( Year, 6, 1) <> '-')
 or   (  substring(Year, 6, 1) = '-'
     and substring(Year, 1, 4) <= '2012')
     and substring(Year, 8, 4) >= '2012'))

Answer 4

Year , - :

SELECT
    *
FROM
    `my_table`
WHERE
    CAST(LEFT(`Year`, 4) AS SIGNED) <= 2012
    AND CAST(RIGHT(`Year`, 4) AS SIGNED) >= 2012

2012 2000 - 2015 .. ..

Answer 5

, LEFT/RIGHT:

SELECT *
FROM (
  SELECT *, CAST(LEFT(Year, 4) AS INT) AS s, CAST(RIGHT(Year,4) AS INT) AS e
  FROM tab
) AS sub
WHERE sub.s <= 2012 AND sub.e  >= 2012;

LiveDemo

Answer 6

, . - . substring_index() :

select t.*
from t
where 2012 >= (year + 0) and -- does silent conversion on the first value in the field
      2012 <= (substring_index(year, ' - ', -1) + 0);

, :

select t.*
from t
where '2012' >= substring_index(year, ' - ', 1) and
      '2012' <= substring_index(year, ' - ', -1);