TSQL return value out of range

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

Quote:

---

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

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

Quote:

---

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

STILL SAM PEOBLEM BECUASE EX
MB100021
MB321344
TR121111


I WANT RECORDS FROM MB100021 TO MB323333

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'