Searching a range - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Searching a range

Author

Topic

rwaldron
Posting Yak Master

131 Posts

Posted - 2009-08-25 : 11:55:48

Hi all
when I use the follwoing statement
select * from table where account BETWEEN ''0000'' AND ''9999''

I get data back greater than 9999 ie: account = 10232 , 11432

If I use
select * from table where account BETWEEN ''0000'' AND ''0099''
data is returned correctly .

Any ideas as to why my first staement is returning data out of the range....P.S I've laso treied <= instead of between with the same results

Thx Ray..

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-25 : 12:01:57

Is account a varchar?

An infinite universe is the ultimate cartesian product.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-25 : 12:06:03

CREATE TABLE #temp

(
tmpinteger int NULL
,tmpString VARCHAR(15) NULL
)

INSERT INTO #temp SELECT 10345, '10345'
INSERT INTO #temp SELECT 345, '345'

SELECT * FROM #temp
SELECT * FROM #temp WHERE tmpinteger BETWEEN 0 AND 9999

SELECT * FROM #temp WHERE tmpString BETWEEN '0000' AND '9999'

An infinite universe is the ultimate cartesian product.

rwaldron
Posting Yak Master

131 Posts

Posted - 2009-08-28 : 05:06:29

Thx Yes..
It is due to the fact that my datayptes are VARCHAR and not INT

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources