CODEWITHSUNDEEP
sql-servert-sqlsortingsql-order-bynatural-sort
Paperwaste
Paperwaste

Reputation: 685

Sorting a string numerically in SQL Server

I have a column in a table that is populated with a string 

level 1, level 2, level 3 ... level 12.

I wish to order by this column but not alphabetically as this orders the column 

1 10 11 12 2 3 4 5 6 7 8 9

How can I order this column to be in correct numerical order despite it being a string type?

I imagine I have to extract only the numerical component of the string and cast it to an int but I don't know how to do this in SQL Server.

Thanks

Upvotes: 1

Views: 160

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44326

Try something like this:

SELECT col
FROM
  (values('level 1'),('level 2'),('level 3'),
  ('level 5'),('level 8'),('level 10'),('level 12')) 
    x(col)
ORDER BY stuff(col, 1,6, '') + 0

The stuff will remove the first 6 characters, the +0 will cast the rest of the col as integer(which can also be done with cast or convert)

Upvotes: 1

Jesuraja
Jesuraja

Reputation: 3844

Try this:

SELECT    *
FROM      TableName
ORDER BY  CAST(REPLACE(ColumnName, 'level', '') AS INT)

Upvotes: 1

mehdi lotfi
mehdi lotfi

Reputation: 11571

Try this:

Select *
From YourTable
Order by CAST( SUBSTRING(YourColumn, 6,LEN(YourColumn)) AS Int)

Upvotes: 1

Related Questions