TSQL return value out of range

[fahedksa]

Declare @fromitem varchar(15), @toitem varchar(15)
set @fromitem=10
set @toitem=60
select categoryid,categoryno ,@fromitem , @toitem from category
where categoryno>=10 and categoryno<=60

thes must return categoryno between 10 and 60
but it return 1 and 2 and 3 and........
Why?

Categoryno varchar(15) becuase there is many items contains letters.
Help Please

[womalley]

Originally posted by fahedksa
Declare @fromitem varchar(15), @toitem varchar(15)
set @fromitem=10
set @toitem=60
select categoryid,categoryno ,@fromitem , @toitem from category
where categoryno>=10 and categoryno<=60

thes must return categoryno between 10 and 60
but it return 1 and 2 and 3 and........
Why?

Categoryno varchar(15) becuase there is many items contains letters.
Help Please

What you did was select everything where the categoryno was greater then or equal to 10 and where categoryno was less then or equal to 60

select categoryid,categoryno ,@fromitem , @toitem from category
where categoryno BETWEEN 10 AND 60

Will

[fahedksa]

i do it as you say but and works fine but when i change the numbers in where line from number to parameter its return out of range data again



Declare @fromitem varchar(15), @toitem varchar(15)
set @fromitem=10
set @toitem=60
select categoryid,categoryno ,@fromitem , @toitem from category
where categoryno BETWEEN @fromitem AND @toitem

[womalley]

Originally posted by fahedksa
i do it as you say but and works fine but when i change the numbers in where line from number to parameter its return out of range data again



Declare @fromitem varchar(15), @toitem varchar(15)
set @fromitem=10
set @toitem=60
select categoryid,categoryno ,@fromitem , @toitem from category
where categoryno BETWEEN @fromitem AND @toitem

Try trimming your string. It looks like you are using numbers so I am not sure why you have your variables set up as strings. Try this

Declare
@fromitem varchar(15),
@toitem varchar(15)
set @fromitem=10
set @toitem=60
select categoryid,categoryno ,@fromitem , @toitem from category
where categoryno BETWEEN LTRIM(RTRIM(@fromitem)) AND LTRIM(RTRIM(@toitem))

Will

[fahedksa]

STILL SAM PEOBLEM BECUASE EX
MB100021
MB321344
TR121111


I WANT RECORDS FROM MB100021 TO MB323333

[Sunil Ranade]

Try this one.......

SELECT categoryid,categoryno ,@fromitem , @toitem FROM category
WHERE CONVERT(INT,LTRIM(RTRIM(SUBSTRING(categoryno,3,15)))) >=100021
AND CONVERT(INT,LTRIM(RTRIM(SUBSTRING(categoryno,3,15)))) <=323333
AND SUBSTRING(categoryno,1,2) = 'MB'