question

vishalsomani avatar image
vishalsomani asked

Text and Numeric values in SQL

Hi All, I am using SQL Server 2008 R2 to run queries and I have come across a database where it stores numeric values as varchar(4). For example: SELECT [num] FROM [TABLE1] WHERE num > '95' /* I get the below results 96 97 98 99 999 */ However when I run the same query without the '' i.e. SELECT [num] FROM [TABLE1] WHERE num > 95 then I get /* 100 101 102 103 104 105 106 107 108 109 110 111 112 113 116 117 120 7001 7002 7003 7004 7005 7006 7007 96 97 98 99 999 */ In any case, I am not getting numbers in order i.e. 95, 96, 97, 98, 99. I understand this is because they are stored as varchar(4) i.e. of a string format. Please can someone explain what happens in both situations and how does a string compare in both the above cases? Also if someone can help me write the code to change these varchar(4) into numeric on the fly so I can arrange them properly? Much appreciated.
text
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
You seem to understand what is happening between the two different queries - i.e. one is a string comparison and one is an integer comparison. By removing the single quotes around 95, you force the query into using implicit conversion ( https://msdn.microsoft.com/en-us/library/ms191530.aspx), so to sort the output into numeric order you need to specify an ORDER BY clause and explicitly convert the values to integers: select num FROM Table1 WHERE num > 95 order by cast(num as int) As long as the data values can be converted to int, then this will work.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.