MS SQL

MS SQL RSS

BETWEEN in WHERE Clause

Please support our MS SQL advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Jun 2007

Posts: 44

Reputation: kshrini is an unknown quantity at this point

Solved Threads: 3

kshrini

Offline

Light Poster

BETWEEN in WHERE Clause

May 20th, 2008

Hi All,

SELECT * FROM Results WHERE ActualValue NOT BETWEEN Lowerimit AND UpperLimit

In my query ActualValue is VARCHAR field. How can I get Results that are out of limit?

Shrinivas

•

Join Date: May 2008

Posts: 6

Reputation: gvalip is an unknown quantity at this point

Solved Threads: 1

gvalip

Offline

Newbie Poster

Re: BETWEEN in WHERE Clause

May 21st, 2008

It looks you have some alpha numeric upper and lower limits. You can look for the pattern of data to create a string and pass it to the query. Another way is to re-phrase the query but I am not too sure of the data and table size. If you can provide some more details I can try and look at it.

•

Join Date: Jun 2007

Posts: 44

Reputation: kshrini is an unknown quantity at this point

Solved Threads: 3

kshrini

Offline

Light Poster

Re: BETWEEN in WHERE Clause

May 21st, 2008

•

Originally Posted by gvalip

It looks you have some alpha numeric upper and lower limits. You can look for the pattern of data to create a string and pass it to the query. Another way is to re-phrase the query but I am not too sure of the data and table size. If you can provide some more details I can try and look at it.

Thanks for reply.
I did it this way:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT * FROM Results
WHERE STID=111 AND (CASE WHEN (ISNUMERIC(ActualValue)=1) THEN CAST(ActualValue AS FLOAT) ELSE 0 END) NOT BETWEEN LowerLimit AND HigherLimit
SELECT * FROM Results
WHERE STID=111 AND (CASE WHEN (ISNUMERIC(ActualValue)=1) THEN CAST(ActualValue AS FLOAT) ELSE 0 END) NOT BETWEEN LowerLimit AND HigherLimit