Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

Your profile

Search

Views

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

tsqlLine number Off | Hide | Select all

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

tsqlLine number Off | Hide | Select all

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

tsqlLine number Off | Hide | Select all

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

tsqlLine number Off | Hide | Select all

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

tsqlLine number Off | Hide | Select all