Ad:

MS SQL Discussion Thread
Marked Solved
Views: 2109

May 20th, 2008

BETWEEN in WHERE Clause

Expand Post »

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

Similar Threads

Throws Clause in Java
Syntax error in FROM clause. in VB.NET
SQL query problem with WHERE clause in ASP
Range of records in Top clause in MS SQL
purpose of exception handling 'finally' clause in Java

kshrini

Reputation Points: 10

Solved Threads: 3

Light Poster

Offline

45 posts
since Jun 2007

Permalink

May 21st, 2008

Re: BETWEEN in WHERE Clause

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.

gvalip

Reputation Points: 10

Solved Threads: 1

Newbie Poster

Offline

6 posts
since May 2008

Permalink

May 21st, 2008

Re: BETWEEN in WHERE Clause

Click to Expand / Collapse Quote 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:

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

Last edited by peter_budo; May 23rd, 2008 at 12:06 pm. Reason: Keep It Organized - please use [code] tags

kshrini

Reputation Points: 10

Solved Threads: 3

Light Poster

Offline

45 posts
since Jun 2007

Permalink

May 21st, 2008

Re: BETWEEN in WHERE Clause

Just curious, how many records are selected out of this query from the total.

gvalip

Reputation Points: 10

Solved Threads: 1

Newbie Poster

Offline

6 posts
since May 2008

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Sybase-Insert getdate()

Next Thread in MS SQL Forum Timeline: One column value to multiple rows

DaniWeb Message

Cancel Changes

User Name
Password