CODEWITHSUNDEEP
sqlsql-servert-sql
Vlad
Vlad

Reputation: 1177

Select row when value is in range

Given the two column below how can I select the row 110-118 if my filter is 111? 100-118 is a range thus 111 falls between 100-118

Dest_ZIP    Ground
004-005     003
009         005
068-089     002
100-118     001

Upvotes: 1

Views: 354

Answers (3)

Marin N.
Marin N.

Reputation: 366

If you wish to make the checks in php, maybe this sample code can help you:

<?php 

$my_string = "111";

$foo = "100-118";  // our range

$bar = explode('-', $foo);  // Get an array .. let's call it $bar
// Print the output, see how the array looks
//print_r($bar);
//echo $bar[0].'<br />';  
//echo $bar[1].'<br />';

if(($bar[0] <= $my_string ) AND ($bar[1] >= $my_string)){ echo 'true';} else { echo 'false';}

?>

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Fix the data. Here is a simple way using computed columns (and assuming the "zips" are always 3 characters):

alter table t
    add column minzip as (left(dest_zip), 3),
    add column maxzip as (right(dest_zip), 3);

Then, you can run the query as:

select t.*
from t
where '111' between t.minzip and t.maxzip;

You can even create an index on computed columns, which can help performance (although not much in this case).

Upvotes: 0

Mike
Mike

Reputation: 550

Below is a simple example of how to do this in SQL using a sub query to get the start and end range. This can be expanded on to better handle parsing the string value.

Declare @Temp TABLE
(
Dest_Zip varchar(7),
Ground varchar(3)
)

INSERT INTO @Temp VALUES ('004-005','003')
INSERT INTO @Temp VALUES ('068-089','002')
INSERT INTO @Temp VALUES ('100-118','001')

SELECT A.Dest_Zip, A.Ground FROM
(
select 
Convert(int, SUBSTRING(Dest_Zip,1,3)) StartNum,
Convert(int, SUBSTRING(Dest_Zip,5,3)) EndNum,
*
from @Temp
) AS A

WHERE 111 >= A.StartNum AND 111 <= A.EndNum

Upvotes: 2

Related Questions