CODEWITHSUNDEEP
sqlsql-serversql-server-2008
THE LIFE-TIME LEARNER
THE LIFE-TIME LEARNER

Reputation: 1522

How to findout a number between two column in SQL Server 2008

Suppose I have table called dbo.tbl_Search

Id    From    To
-------------------
1     200     210
2     212     220
3     203     215
4     225     240

Suppose, I search for No.-205 then I would to like result like this

Id    From    To
-------------------
1     200     210
3     203     215

Because 205 No. falls between this two ids.

Note: I don't have any other table to join with this.

I have tried this SQL but it's bot working:

SELECT * 
FROM dbo.tbl_Search 
WHERE 
    (ISNULL(From, 0) = (CASE WHEN ISNULL(205, 0) = 0 
                                THEN ISNULL(From, 0) 
                                ELSE ISNULL(205, 0) 
                        END ) 
    OR ISNULL(To, 0) = (CASE WHEN ISNULL(205, 0) = 0 
                                THEN ISNULL(To, 0) 
                                ELSE ISNULL(205, 0) 
                        END ))

Upvotes: 0

Views: 58

Answers (3)

Umair Zafar
Umair Zafar

Reputation: 90

It should be simple number comparison as per your question.

SELECT *
FROM tbl_Search
WHERE FROM <= 205 AND TO >= 205

Upvotes: 0

Aquib
Aquib

Reputation: 23

Not sure if I understand this correctly but shouldn't a simple query like below work

SELECT * 
FROM dbo.tbl_Search 
WHERE From >= 205 AND To <= 205

Upvotes: 1

PTank
PTank

Reputation: 71

SELECT * 
FROM dbo.tbl_Search 
WHERE ISNULL(@number, 0) BETWEEN FROM AND TO

Using between operator you can find a records between range.

Upvotes: 1

Related Questions