Sorting Numbers Stored In A Varchar Column
From Wiki
You inherited a database where they store integers in a varchar column(I am saying inherited because someone like you would never ever do such a design mistake right? :mrgreen: ) What happens when you do an order by on a varchar column which stores integers? Well for one 112 will be returned before 12. Let's take a look, first create this table
- CREATE TABLE #VarcharSort(Num VARCHAR(15))
- INSERT #VarcharSort VALUES('12')
- INSERT #VarcharSort VALUES('112')
- INSERT #VarcharSort VALUES('12')
- INSERT #VarcharSort VALUES('122')
- INSERT #VarcharSort VALUES('122')
- INSERT #VarcharSort VALUES('122')
- INSERT #VarcharSort VALUES('122')
- INSERT #VarcharSort VALUES('1222222')
- INSERT #VarcharSort VALUES('122122122123')
Now run this
- SELECT * FROM #VarcharSort
- ORDER BY Num
Here is the output
| 112 |
| 12 |
| 12 |
| 122 |
| 122 |
| 122 |
| 122 |
| 122122122123 |
| 1222222 |
In order to 'correctly' sort we need to convert to an integer datatype. I used bigint
- SELECT * FROM #VarcharSort
- ORDER BY CONVERT(BIGINT,Num)
And now the output is this
| 12 |
| 12 |
| 112 |
| 122 |
| 122 |
| 122 |
| 122 |
| 1222222 |
| 122122122123 |
Why did I use bigint? Well if you run this
- SELECT * FROM #VarcharSort
- ORDER BY CONVERT(INT,Num)
You will get this error message
Server: Msg 248, Level 16, State 1, Line 1
The conversion of the varchar value '122122122123' overflowed an int column. Maximum integer value exceeded.
you can also sort by the first 4 characters only. Like this
- SELECT * FROM #VarcharSort
- ORDER BY CONVERT(INT,LEFT(Num,4))
Output
| 12 |
| 12 |
| 112 |
| 122 |
| 122 |
| 122 |
| 122 |
| 122122122123 |
| 1222222 |
Contributed by: --SQLDenis 02:16, 31 May 2008 (GMT)
Part of SQL Server Programming Hacks
Section Sorting, Limiting
