Login or Sign Up to become a member!

EXPERTS, INFORMATION, IDEAS & KNOWLEDGE

Social bookmarker Add this

Sorting Numbers Stored In A Varchar Column

From Wiki

Jump to: navigation, search

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


  1. CREATE TABLE #VarcharSort(Num VARCHAR(15))
  2.     INSERT #VarcharSort VALUES('12')
  3.     INSERT #VarcharSort VALUES('112')
  4.     INSERT #VarcharSort VALUES('12')
  5.     INSERT #VarcharSort VALUES('122')
  6.     INSERT #VarcharSort VALUES('122')
  7.     INSERT #VarcharSort VALUES('122')
  8.     INSERT #VarcharSort VALUES('122')
  9.     INSERT #VarcharSort VALUES('1222222')
  10.     INSERT #VarcharSort VALUES('122122122123')


Now run this

  1. SELECT * FROM #VarcharSort
  2.     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

  1. SELECT * FROM #VarcharSort
  2.     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

  1. SELECT * FROM #VarcharSort
  2.     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

  1. SELECT * FROM #VarcharSort
  2.     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

130 Rating: 2.4/5 (5 votes cast)