CODEWITHSUNDEEP
mysqlsql
bigmike7801
bigmike7801

Reputation: 3970

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

I am working with a table that is using year ranges for some of the data I need to be able to select a record by a year that falls between those 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 vehicles with a year of 2012.

This should return: 2, 3, 4 and 6 given the example table below.

Upvotes: 4

Views: 94

Answers (6)

Matt
Matt

Reputation: 15061

Use LEFT and RIGHT to determine the 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:

Upvotes: 2

Rajib Ghosh
Rajib Ghosh

Reputation: 640

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

For this purpose, I would first try to restructure the database so you have two separate fields for the maximum and minimum years. However, sometimes we are stuck with someone else's bad design decisions. In this case, substring_index() with silent conversion is useful:

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);

If you input the year as a string, you don't need the silent conversion:

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

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175756

You can use get start and end year using 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

Upvotes: 0

Tony DeStefano
Tony DeStefano

Reputation: 829

If the Year column is guaranteed to always have one or two years, then you could do something like this:

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

This would catch a column with 2012 or with 2000 - 2015 etc, etc

Upvotes: 0

PSVSupporter
PSVSupporter

Reputation: 348

Probably something like this: Of course you have to check the table if all columns with year-ranges are in the same context, else this won't work.

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'))

Upvotes: 0

Related Questions