how to get exact between values in sql server 2008

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hi,

I have a table with 2 columns minexperience and maxexperience as varchar(max)
those values are

minexperience   maxexperience 
1                   4
2                   5
1                   3
8                   9

when i am retrieving by passing two parameter values those are @minexp and @maxexp

I wrote the query as follows

SQL

select *from table where minexperience between @minexp and @maxexp and maxexperience between @minexp and @maxexp

then I am getting wrong output.

when iam passing 1 and 3 getting correct output.
but when iam passing 8 and 9 getting no output

how can i write the query

Posted 18-Oct-11 23:45pm

Member 7932936

Updated 19-Oct-11 1:08am

Toniyo Jackson

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2011-10-18T23:58:00

Solution 2

The problem is probably that your data is not what you think: never store numbers as strings if you intend to do any match with them - use a number format instead. The chances are that your "8" or "9" is not just "8" or "9" - it may have leading spaces for example.

Use an Int datatype instead.

Posted 18-Oct-11 23:58pm

OriginalGriff

Comments

Reiss 19-Oct-11 6:07am

Good catch

Member 7932936 19-Oct-11 6:11am

haven't spaces but all large and small no it's working fine but problem with only 8 and 9 only

Reiss · Answer 2 · 2011-10-18T23:52:00

Solution 1

You only need this surely

SQL

select *from table where minexperience >= @minexp and maxexperience <= @maxexp

Posted 18-Oct-11 23:52pm

Reiss

Comments

Member 7932936 19-Oct-11 6:02am

I write like above query it's working.
but when iam passing minexp >=8 and maxexp <=9
then i have to get one row

but my result is null

sachin10d · Answer 3 · 2011-10-19T01:03:00

This works fine

SQL

create table yourTableName(minexperience nvarchar(max),   maxexperience nvarchar(max))
insert into yourTableName
select 1,                           4
union all select 2,                           5
union all select 1,                           3
union all select 8,                           9

declare @minexp nvarchar(max) = 8, 
		@maxexp nvarchar(max) = 9
select *from yourTableName where minexperience >= @minexp and maxexperience <= @maxexp

we get the output as

HTML

minexperience    maxexperience
---------------- ----------------
8                9