newbie question - 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

General SQL Server Forums

New to SQL Server Programming

newbie question

Author

Topic

unikoman
Starting Member

32 Posts

Posted - 2006-02-20 : 07:08:53

I have a table
that has a column (prod_id) in it I have values ('9','87','90','6','7725','11235','2345')

when I run this query:

select*
from products_tbl
where prod_id >'2345';

it returns values 6, 7725, 87, 9,90..........whats wrong with my query?
I thought only values greater than 2345 were suppost to be returned.

Thanks again for your help.

Kristen
Test

22859 Posts

Posted - 2006-02-20 : 07:33:29

Sounds like prod_id is a string/varchar datatype column, rather than an integer - so SQL is making a "text" comparison, and:

6: '6' > '2' so true
7725: '7' > '2' so true

and so on

Kristen

unikoman
Starting Member

32 Posts

Posted - 2006-02-20 : 07:37:59

correct, I made it a varchar...now I know not to..next time...... thanks

Kristen
Test

22859 Posts

Posted - 2006-02-20 : 07:39:53

If would be more efficient to fix the datatype, but you could work around the problem with:

select*
from products_tbl
where CONVERT(int, prod_id) > 2345

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources