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
.
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
.
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
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.
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)))
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.