7
I have a name field in a table that has the following data:
1-Course
2-Course
10-Course
When ordering by name it returns 1,10,2. How to make order to bring: 1,2,10 ?
Ps: The field is NVARCHAR.
3 answers
6
If the field format always follows the same pattern, you can capture the part before the dash and convert to a number, 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 the Sqlfiddle
However, consider that this is a very inefficient solution since it will not make use of indexes in the database.
Ideally you would create another numeric column and store the sort number in it at the time of recording or updating the record.
4
As it was not passed the layout of the table I put name and table in the relative places:
SQL
SELECT SUBSTRING(nome, 0, CHARINDEX('-', nome)), nome
FROM tabela ORDER BY CONVERT(INT, SUBSTRING(nome, 0, CHARINDEX('-', nome)))
3
Part of the question you already answered: The field is nvarchar, so it sorts alphabetically character by character.
To order the way you want it is necessary to first extract the numeric part and turn into int.
Assuming that 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 into two, a numerical part and another text part, to avoid this type of query.
-
2
pq o downvote ?
-
I voted for it right ...!!! now who voted against I do not know.
Browser other questions tagged sql sql-server order
You are not signed in. Login or sign up in order to post.
I would say that, given the scenario proposed by the PO, this is the most efficient solution.
– OnoSendai
@Onosendai, what are the reasons that lead me to believe that this solution is the most efficient, based on what (sincerity, I came up with this doubt)?
– Maria
@Mary the key term is 'proposed scenario'. Given the limitations imposed by the OP, I can not imagine another solution that is shorter, direct or simple.
– OnoSendai
@Onosendai I thought it was based on something technical since the two answers below do not escape that much... but, thank you !!!
– Maria
@Maria was another comment on an excerpt of the answer: '[...]consider that this is a very inefficient solution[...]'. It was not a comparison with the other replies; they were all based on the same principle of Parsing and conversion.
– OnoSendai
That’s why I asked the question @Onosendai wanted to understand why maybe being more efficient, was even technically speaking, learning... understood!
– Maria