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

S.Serpooshan
  • 7,608
  • 4
  • 33
  • 61
Mihai Stancioiu
  • 794
  • 1
  • 5
  • 12

3 Answers3

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

peterm
  • 91,357
  • 15
  • 148
  • 157
  • 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. – Mihai Stancioiu Dec 15 '13 at 07:53
  • 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. – peterm Dec 15 '13 at 08:07
  • Ok. I will give up this sort. Thanks – Mihai Stancioiu Dec 15 '13 at 08:10
  • 1
    Take a look at execution plans for both queries in this [sqlfiddle](http://sqlfiddle.com/#!3/e18c8/1) I created for you. You can always consider to split your columns values into two separate columns and do this splitting on insert/update. – peterm Dec 15 '13 at 08:15
  • 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. – Mihai Stancioiu Dec 15 '13 at 08:32
  • About trimming: Unfortunately you never showed your sample data from the column, so I'm not sure whether you need to split values or trim them. If you have numeric values with spaces around you don't need them trim - just do CAST( AS INT). Performance wise it still sucks because you touch the value effectively causing full scan. – peterm Dec 15 '13 at 08:39
  • Yes, I have numeric values with spaces around and I need it sorted as string with space removed like in the second ex. from here: http://sqlfiddle.com/#!3/ce7c2/1, but I would thank if I could sort them as int too. From what i understand in this discussion none of the solution are optimal so I'll give up. I will not sort that column until other improvements are made to the database. – Mihai Stancioiu Dec 15 '13 at 08:50
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
bjnr
  • 3,353
  • 1
  • 18
  • 32
  • 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.. – Mihai Stancioiu Dec 15 '13 at 08:29
  • @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. – bjnr Dec 15 '13 at 08:33
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),'')
gotqn
  • 42,737
  • 46
  • 157
  • 243
  • @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'. – bjnr Dec 15 '13 at 08:36
  • 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? http://sqlfiddle.com/#!3/ce7c2/1 – Mihai Stancioiu Dec 15 '13 at 08:38
  • @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 Dec 15 '13 at 08:53
  • @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 – bjnr Dec 15 '13 at 12:14
  • @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: http://sqlfiddle.com/#!3/ce7c2/2 – Mihai Stancioiu Dec 15 '13 at 13:16
  • @MihaiStancioiu: Ups! I didn't expect to have spaces in the beginning... Just to point out a slight difference: ORDER BY st will take advantage of a index defined on st column, while ORDER BY function(st) cannot use that index. – bjnr Dec 15 '13 at 13:35