CODEWITHSUNDEEP
sqlsql-serversorting
Helgegren
Helgegren

Reputation: 137

sql sorting column with both number and text

I'm trying to arrange a column with values like:

1 test
amazon
11 basic
2 hi
02toad
ikea
22
burger 
002 paper

I want them to be arranged like this:

1 test
2 hi
002 paper
02toad
11 basic
22
amazon
burger 
ikea

So i what i want is to first sort on number and then on the text. Not all values do contains numbers and not all contains text.

Upvotes: 0

Views: 235

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

What you want to do is tricky in SQL Server, but you can do it:

order by try_convert(bigint, left(col, patindex('%[^0-9]%', col + 'x'))),
         col

You need to convert the leading digits to a number for this to work, because your ordering treats the initial digits as a number.

That actually puts the letters before the numbers. This is easily remedied with another key:

order by (case when col like '[0-9]%' then 1 else 2 end),
         try_convert(bigint, left(col, patindex('%[^0-9]%', col + 'x'))),
         col

In general, if you are trying to interpret a number at the beginning of the string as a number, then you probably want the value in a different column. In other words, you might consider fixing your data model as well.

Upvotes: 2

Related Questions