Search code examples
sql-server-2005intvarchar

SQL Server 2005 Varchar less than

I've googled this a bit and can't seem to find a good answer ...

I have a table with a varchar(4) column that contains 41 rows with 90 and 8 rows with 090.

Query 1 will return 9 rows, 8 rows with 90 and 1 with 090

SELECT * 
FROM WorkArea..BSNOB_ServiceSalesParts_Testing
WHERE Source < '100'
ORDER BY Source DESC

Query 2 will return all 49 rows, although the rows containing 090 are at the bottom of the results.

SELECT * 
FROM WorkArea..BSNOB_ServiceSalesParts_Testing
WHERE Source < 100
ORDER BY Source DESC

Can anyone explain to me why query 1 is missing data? And also why query 2 returns all data, but has 090 below 90 data? I figure this has something to do with conversion but I can't find any documentation explaining this.

Thank you.

Solution

  • Query 2 displays the '090' data beneath the '90' data because that's what you're telling it to do in the ORDER BY aspect.

    Remember, your SOURCE column is a VARCHAR. It's a string, despite being numbers, and as such will sort according to the rules of strings. Thus, it's looks at the first character for order, rather than to the numerical value.

    If you want it to sort according to the numerical value which the string represents, then you'll need to cast the SOURCE column to an integer in the ORDER BY statement.

    And as for why it returns all data, that's because you're comparing the numerical representation of a number to a number. SQL performs an implicit conversion within the where clause to deal with the boolean operator.

    Now, your reported results for Query 1 are not what I'd expect. I'd expect for you to only get the 8 '090' rows, as those are the rows that would be less than the string '100' (and this is the result in SQL2012 as I just tested).

    But to deal with why the results of the two queries are different, since you explicitly built the '100' as a string, it performs the comparison as a string and does not perform any conversions. 0 comes before 1 comes before 9 in string compares. It doesn't care that they are numbers, when the first character differentiates them enough for filtering.