sortorder on varchar datatype column containing numeric values

Question

Post reply

sortorder on varchar datatype column containing numeric values

Grant_S

SSC Veteran

Points: 225
More actions
May 2, 2005 at 1:58 pm

#68983

Hi
Sorting a column ascending with varchar data types that contain numeric values returns unexpected results (incorrect order). For instance if values such as 107 and 14 are contained in the results returned, then if the sortorder was ASC, 107 appears before 14, not after - as expected. What is the best way to implement returning the results in the expeced numberic ascending order?
Thanks in advance.
Grant

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 1

throw in a convert in the order by clause.

SELECT

FROM

<table>

ORDER BY

CONVERT( INT, <column> )

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Please post some sample data.

Are the numbers always at the beginning, somewhere in between or at the end of the string?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Antares686 SSC Guru Points: 125444 More actions · Answer 3

The problem you have is that your data is not sorted the way you assume and is based on the rules of the data type in use.

If all the data is numeric and that is all it ever will be then you should consider changing the field to and numeric data type such as decimal or int then the issue will be moot.

However that said as the data is stored as character data the sort will be based on the character sorting rules.

As such if the data contained

All and Access and Bernard it will sort

Access

All

Bernard

Becuase the charcters are sorted 1 position at a time. The same is true with numbers stored in character type fields

1 and 2 and 114 will sort

1

114

2

because the first character decides sort position first then the next character and so on.

Now if you cannot change your data type but the rule of all are numbers is still true then using the CONVERT method mentioned before should help but you really should use CAST

ORDER BY CAST(<column> as int)

From BOL

Use CAST rather than CONVERT if you want Transact-SQL program code to comply with SQL-92. Use CONVERT rather than CAST to take advantage of the style functionality in CONVERT.

Now if you have data mingled in that is not numeric then you will have a lot of trouble and will not be able to sort as you want unless you apply leading 0's out to the maximum lenght of the data in the table.

So if you have a varchar(8) column to sort the numeric data properly you can do like so

'

ORDER BY

(CASE WHEN IsNumeric(<column&gt = 1 THEN RIGHT(replicate('0',8) + RTRIM(<column&gt,8) ELSE <column> END)

With that when 1 and 2 and 114 occurr the data looks for sorting like this.

00000001

00000002

00000114

This will be transparent to anyone looking at it as the data for the column is output as normal unless you apply some special formating or other concatination.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

Actually, this is very much expected sorting behaviour.

Remember that computers never makes any assumptions, they just do what you tell them to. In addition to this, when you deal with strings - ie '107' and '14', you must be aware of how that looks to the comp.

Letters aren't seen as 'a', 'b', '1' or '7' - they are seen as their numerical ASCII values. Thus, when you sort '107' and '14', you're really asking the order of 494855 and 4952. AS we can see, 494855 is larger than 4952, so when sorted ascending, it comes first. '107' is ASCII codes 49, 48 and 55. What this means in reality, is that the datatypes involved makes all the difference. Strings doesn't sort like numbers do, even if we might read '107' as a numeric.

See the pattern?

/Kenneth

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Actually rereading the thread and the "best" advise here is to split numbers from strings into two columns. What you describe is indeed expected behaviour. There are, of course, workarounds possible to get what you expect. that's why I said, post sample data. But every workaround is a kludge at best.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 6

I was merely trying to point out the fine difference between 'expected' and 'intended'... What we intend may not always be what we can expect

/Kenneth

noeld SSC Guru Points: 96590 More actions · Answer 7

noeld

SSC Guru

Points: 96590

May 3, 2005 at 9:32 am

#556565

... and viceversa

* Noel

Sergiy SSC Guru Points: 110208 More actions · Answer 8

Lets say that your column data type is varchar(50).

In such case it gonna be:

ORDER BY case when ISNUMERIC(ColumnName) = 1 then REPLICATE ('0', 50 - LEN(LTRIM(ColumnName))) + LTRIM(ColumnName) else ColumnName end

It will bring you desired order, but it gonna be really slooooow, because it cannot use any index.

Suggession: use right datatype.

_____________
Code for TallyGenerator