Sorting string column containing numbers in MSSQL?

Question

I have the same problem as described in the following link:

Sorting string column containing numbers in SQL?

I have a string column with numeric data that have white spaces both at beginning and at the end of column and I need to sort it.

The solution I found (in MySQL) is:

SELECT * 
  FROM data 
 ORDER BY left(st,LOCATE(st,' ')), 
          CAST(SUBSTRING(st,LOCATE(st, ' ')+1) AS SIGNED)

My question is if this solution is optimal and don't create huge load if I convert it to Mssql and run it on a table with more than 100.000 records.

Also please suggest me a conversion to Mssql because I am not very familiar with mssql.

Thanks

Answer 1

A SQL Server version of above-mentioned MySQL query might look like

SELECT *
  FROM table1 
 ORDER BY LEFT(name, CHARINDEX(' ', name)),
          CAST(RIGHT(name, LEN(name) - CHARINDEX(' ', name) + 1) AS INT)

Here is SQLFiddle demo

We never saw your sample data but if you just have numeric values with leading and/or trailing spaces in that column you can just do

SELECT *
  FROM table1
 ORDER BY CAST(name AS INT)

Here is SQLFiddle demo

Answer 2

You might have some performance problems in sense that ORDER BY clause will not take advantage of indexes you probably have defined on.

My suggestion is to split that column in 2 columns and change the ORDER BY clause into a simple one:

ORDER BY Name1, Name2

In order to keep your application code unchanged, you can add a computed column:

ALTER TABLE <tablename> ADD Name AS Name1 + ' ' + Name2

Answer 3

You can simple use RTRIM and LTRIM:

SELECT * 
FROM data 
ORDER BY RTRIM(LTRIM(st))

An alternative solution will be to REPLACE all spaces in the your column like this:

SELECT * 
FROM data 
ORDER BY REPLACE(st,CHAR(32),'')