Order SQL for 1,2,3 instead of 1,10,2,3

7

I have a name field in a table that has the following data:

  

1-Course

     

2-Course

     

10-Course

When sorting by name it returns 1,10,2 . How to order to bring: 1,2,10 ?

Ps: The field is NVARCHAR .

    
asked by anonymous 12.08.2014 / 23:26

3 answers

6

If the field format always follows the same pattern, you can capture the part before the dash and convert it to a number, and then sort the result by this value.

Example:

select *, 
  CONVERT(INT, LEFT(nome, CHARINDEX('-', nome) - 1)) AS ORDEM
from TESTE
order by ORDEM

Demo on SqlFiddle

However, consider this a very inefficient solution since you will not use indexes in the database.

Ideally you would create another numeric column and store the sort number on it at the time of writing or updating the registry.

    
13.08.2014 / 00:04
4

As the table layout was not passed I put the name and table in the placeholders:

SQL

SELECT SUBSTRING(nome, 0, CHARINDEX('-', nome)), nome
FROM tabela ORDER BY CONVERT(INT, SUBSTRING(nome, 0, CHARINDEX('-', nome)))

SQLFiddle

    
13.08.2014 / 00:12
3

Part of the question you have already answered: The field is nvarchar , so it sorts alphabetically character by character.

To sort the way you want it is necessary first to extract the numeric part and transform it into int .

Assuming this column follows a pattern (number followed by a dash, followed by something), you can do something like this:

SELECT curso 
FROM tabela
ORDER BY CONVERT(INT, LEFT(curso , CHARINDEX('-', curso) - 1))

However, if possible, break your column in two, one numeric part and another part text, to avoid this type of query.

    
13.08.2014 / 00:07