CODEWITHSUNDEEP
sql-servert-sql
Shamvil Kazmi
Shamvil Kazmi

Reputation: 443

How to query from range?

I have a table like this

CREATE TABLE Table1
    ([range] varchar(9), [sector] int)
;
    
INSERT INTO Table1
    ([range], [sector])
VALUES
    ('684-733', 2),
    ('563-598', 3),
    ('514-544', 2),
    ('640-682', 3),
    ('1053-1152', 2)
;

I want to get information by passing a predicate So far I have this

select sector from table1 where [range] = 564

expected outcome

3

Is there any function I can use to get the data?

Upvotes: 0

Views: 620

Answers (4)

ListenSoftware Louise Ai Agent
ListenSoftware Louise Ai Agent

Reputation: 4243

use a cross apply and a case statement to find a range where 564 is between the start and end values

 select
  tbl1.range,
 case when 564 between Lookup.startValue  and Lookup.endValue then
    tbl1.sector
 end Sector
 from @tbl tbl1
 cross apply
 (
 select 
 tbl2.range,
 tbl2.sector,
 cast(left(tbl2.range,charindex('-',tbl2.range)-1) as int) startValue,
 cast(right(tbl2.range,len(tbl2.range)-charindex('-',tbl2.range)) as int) endValue
 from @tbl tbl2
 where tbl1.range=tbl2.range
 )Lookup
 where
 case when 564 between Lookup.startValue  and Lookup.endValue then
   tbl1.sector
 end is not null

output:

  range Sector
  563-598   3

Upvotes: 1

miriamka
miriamka

Reputation: 481

Please try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, [range] varchar(9), [sector] int);
INSERT INTO @tbl ([range], [sector]) VALUES
('684-733', 2),
('563-598', 3),
('514-544', 2),
('640-682', 3),
('1053-1152', 2);
-- DDL and sample data population, end

DECLARE @param INT = 564;

;WITH rs AS
(
    SELECT *
        , LEFT([range], pos -1) AS [start]
        , RIGHT([range], LEN([range]) - pos) AS [end]
    FROM @tbl
        CROSS APPLY (SELECT CHARINDEX('-', [range])) AS t(pos)
)
SELECT sector 
FROM rs
WHERE @param BETWEEN [start] AND [end];

Output

+--------+
| sector |
+--------+
|      3 |
+--------+

Upvotes: 2

eshirvana
eshirvana

Reputation: 24568

in SQL Server 2016 and later you can use string_split :

select sector
from table1 t1
cross apply string_split(t1.range, '-') y
group by sector 
having 564 between min(y.value) and max(y.value)

db<>fiddle here

Upvotes: 1

blecovich
blecovich

Reputation: 106

Storing a number as a string like this could cause many issues later. Your best move is refactoring the table as follows:

CREATE TABLE Table1
([beginrange] int, [endrange] int, [sector] int)

Insert into table1 values
(684, 733, 2)
...

You would then get your result with:

select sector from table1 where 564 between [beginrange] and [endrange]

That said, if you do not have control over this table, you'll need to parse the string into two integers:

select * from table1 where 580 between convert(int, substring([range], 0, charindex('-', [range]))) and convert(int, substring([range], charindex('-', [range]) + 1, len([range])))

You can look up the various functions used here.

Upvotes: 1

Related Questions