1

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

Improve this question
3
  • MSSQL or MySQL ? which is it? Commented Dec 15, 2013 at 7:27
  • I want in Mssql, but i dont'n know and I test in Mysql. But I am not sure if this is an optimal solution and also how to transform it to mssql. Commented Dec 15, 2013 at 7:29
  • Can you show some sample values from your column? Commented Dec 15, 2013 at 7:42

3 Answers 3

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Thanks very much. What do you think. Will this sort create big load and should be avoided on a big database or it's ok to use it. I rather don't sort that column instead of making database crush.
Performance wise it's a nightmare. You'll always have explicit sort operation in your execution plan that should process all rows in your resultset.
Ok. I will give up this sort. Thanks
Take a look at execution plans for both queries in this sqlfiddle I created for you. You can always consider to split your columns values into two separate columns and do this splitting on insert/update.
I can't modify database structure... I only have access to a stored procedure that I access in my application and I am trying to find solution for that. What about the solution with trimming data and sort as string. It is more useful for me, but i think it might be a worst solution for the query.
|
0

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),'')
Improve this answer

6 Comments

@MihaiStancioiu: There is a difference between sorting as string, respectively as int. For example, having 'a 1', 'a 2', 'a 13', sorting as int will return : 'a 1', 'a 2', 'a 13', but sorting as string will return: 'a 1', 'a 13', 'a 2'.
Yes I know there is a difference. I actually need to sort them as string (second version in the following link) but I thought the query will be faster with int... So will it be ok to trim column and sort as string? sqlfiddle.com/#!3/ce7c2/1
@MihaiStancioiu, if you need to sort the column as string it's all right to use the trim functions or replace function. You are free to cast the result of both technique as INT if you need. I can be sure, but believe this is faster then using CHARINDEX, LEFT and RIGHT functions, but it is up to use to check the execution plans and decide.
@gotqn: I'm thinking there is no need to replace spaces, as the sort order will not change. So ORDER BY RTRIM(LTRIM(st)) is identical with ORDER BY st
@SQLhint.com: It is not correct what you are saying. Records with space in front will be put first and it's not ok. Take a look a the folowing ex: sqlfiddle.com/#!3/ce7c2/2
|
0

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
Improve this answer

2 Comments

I can't split the column... I share that Db with other application. Actually I did not have access to that database. I just only call a stored procedure in application (website). I only try to find a solution for that procedure... What about the example below with TRIM and sort values as string. It's better for me if a i sort as string but I am pretty sure that this will cause huge load too. Maybe more..
@MihaiStancioiu: In your context, solution provided by peterm is probably the single one. In the future, you might want to store those values in separate columns. Regarding TRIM column, I will a comment there.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.