Question 212903 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Match value with range value in column

I am having table with column registration year with values like ‘2017-2019’ and ‘2014-2016.

If my input value is 2018 then what will be my SQL query to get that record which is in between ‘2017-2019’.

id registration_year
1 2014-2016
2 2017-2019

above is DB table and my input is 2018. Then it should return id 2.

What will be my sql query.
Thanks in advance

Tags:

5

Answers


  1. 0 
    ... WHERE @input_value BETWEEN SUBSTRING_INDEX(column, '-', 1) AND SUBSTRING_INDEX(column, '-', -1)
    Login or Signup to reply.
  2. 0

    Honestly now would be a good time to change your table design. Consider this version, which stores the registration start and end years in different columns:

    id | registration_start | registration_end
1  | 2014               | 2016
2  | 2017               | 2019

    With this properly designed table in place, now you only need a simple range comparison:

    SELECT *
FROM yourTable
WHERE registration_start <= 2018 AND registration_end >= 2018;
    Login or Signup to reply.
  3. 0

    I agree with TB’s answer where you should redesign your table structure. Here’s a solution with the current structure.

    select id
from my_table 
where left(registration_year, 4)::integer <= 2018
  and right(registration_year, 4)::integer >= 2018;
    Login or Signup to reply.
  4. 0

    To retrieve the record that matches the input year (2018) with the registration year ranges in the SQL table, you can use the following SQL query with the BETWEEN operator:

    SELECT 
  id, registration_year 
FROM your_table_name 
  WHERE 
    2018 BETWEEN 
      SUBSTRING(registration_year, 1, 4) AND
      SUBSTRING(registration_year, 6, 4);
    Login or Signup to reply.
  5. 0

    If you always, or almost always, start and end together then rather than a string define the column as a integer range. This still results in a single column. Then query with the range containment operator (<@). So: (See demo)

    create table my_table( id   integer  generated always as identity
                                     primary key 
                     , registration int4range
                     );
select * 
  from my_table 
 where <input_year> <@ registration;

    A note on demo result. When queried the registration column will appear as [2017,2020). This is correct for your data values (row 2). That is because when queried Postgres always responds as contains the lower bound but does not contain the upper bound. However you will notice that row was created as contains lower bound and contains upper bound.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search