6

I have a column called Number populated with this data (column is nchar):

1
2
1091
3
20
2B

I want a select statement that give this order:

1
2
2B
3
20
1091

How I can accomplish this? Thanks everybody for your help

(EDITED)

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
BernieSF
  • 1,722
  • 1
  • 28
  • 42

3 Answers3

18

You can perform some tricks by converting to a numeric after you discover the location of the first non-numeric. Appending a random character at the end makes it treat all strings the same even if the original string did not contain an alphabetic.

SELECT [Number] FROM dbo.TableName 
ORDER BY CONVERT(INT, LEFT(Number, PATINDEX('%[^0-9]%', Number + 'z')-1));
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Aaron - I do think it is important to give future visitors to the question the right terminology to use. Sometimes a link to documentation is a bonus and this case, almost required. A search for `Sql Server` and `convert` pulls up a lot of false hits.. I *think* it converts to an integer. The `-1` in your expression confuses me totally. IF the code does what I /think/ it does -- that is, extract out just the numeric part of the `Number`, then `32a`, `32fred` and `32c` would come out unordered -- since the alphabetic part isn't considered. – Jeremy J Starcher Sep 21 '12 at 22:45
  • 1
    @Jeremy a solution to a coding question should not have to include the definition for every single function used in the solution. Do I really need to write a paragraph on what CONVERT does? The OP certainly didn't think so, and you are probably the only reader who would expect that, too. If further sorting is *required* then it should be stated in the question. If I *assume* that a certain sorting should be required, as you suggest, then the answer does *more* than the OP asked. So in the end, do I think this answer helps the OP more than you pointing to Oracle and MySQL natural sorts? Yes. – Aaron Bertrand Sep 21 '12 at 22:48
  • @AaronBertrand -- **So in the end, do I think this answer helps the OP more than you pointing to Oracle and MySQL natural sorts? Yes** This was never under discussion. – Jeremy J Starcher Sep 21 '12 at 23:15
  • @Jeremy pretty sure down below you said that an answer pointing to documentation was better than a "magic incantation"... should one not assume you were also suggesting that your answer with links to natural join techniques for other RDBMSes was also better? – Aaron Bertrand Sep 21 '12 at 23:18
  • @AaronBertrand -- Since you provided an exact solution, and I could only provide a *here is the next step in your search for an answer*, there was no question that your answer was better. **However** -- had no correct answer come up, then the term *Natural Sort* (which I provided) would have at least given the OP the name of what he needed. Depending on his research and database skills, the OP could have used the techniques I pointed to to form his own solution. – Jeremy J Starcher Sep 21 '12 at 23:25
1

What you are looking for is called a NATURAL SORT, which is different from the regular sort that most systems give. I've found a handful of posts here at Stack Overflow that covers natural sorts in various popular SQL engines, including one brief discussion about SQL Server.

The ideas here may get you started on a solution.

Oracle: How can I implement a "natural" order-by in a SQL query?

You might have to change the syntax for your SQL engine though.

Found another version for MYSQL:

Natural Sort in MySQL

Can't find any for SQL Server yet.

Edit3

Ah, this one covers some SQL Server ideas too:

Natural Sort in MySQL

Community
  • 1
  • 1
Jeremy J Starcher
  • 23,369
  • 6
  • 54
  • 74
  • @AaronBertrand -- The OP needed a natural sort, though the OP didn't use that term my name. Since SQL engines don't have a natural sort built in, one has to be made using an expression. My links covered different ways people have gotten a natural sort in various forms of SQL. One of them could have been adapted. – Jeremy J Starcher Sep 21 '12 at 21:17
  • 2
    Well they didn't need it in Oracle or MySQL, and the MySQL answer doesn't seem to have any valid answers for SQL Server either, as you suggest. The closest I found was one that contained `MID` but that's an Access function, not SQL Server. – Aaron Bertrand Sep 21 '12 at 21:20
1

Use PATINDEX, check this SQL Fiddle code. @AaronBertrand just answered as I was testing.

Just to add some more value and info, check this SQLServerCentral link as there you can find more answers.

And check this Natural (human alpha-numeric) sort in Microsoft SQL 2005, lot of useful answers and variations.

Community
  • 1
  • 1
Yaroslav
  • 6,476
  • 10
  • 48
  • 89
  • Can some of the downvoters comment on what is wrong? I was just adding some more information on the already useful answer...thanks – Yaroslav Sep 21 '12 at 21:19
  • 2
    EH, someones been down-vote happy on this question. Have a +1 for providing documentation, not just a magic incantation answer. – Jeremy J Starcher Sep 21 '12 at 21:21
  • 1
    I didn't down-vote (yet) but usually if you are trying to add information to an existing answer, you add a comment, not an additional answer. – Aaron Bertrand Sep 21 '12 at 21:21
  • @AaronBertrand, when you posted your answer I was about to post mine, see the time difference. Then I took some time to add these links. Is not the first time I see same or almost same answer with less than minute difference. And even comments like "beat me for X seconds" and alike. – Yaroslav Sep 21 '12 at 21:23
  • @JeremyJStarcher what answer on this page is from magic incantation? – Aaron Bertrand Sep 21 '12 at 21:50
  • @AaronBertrand -- `Incantation` is a slang term used in some circles for a technically correct answer that offers no explanation as to *why* a given answer works. While I have no idea that your answer does exactly was the OP asked for, it doesn't help him solve similar problems in the future. – Jeremy J Starcher Sep 21 '12 at 22:21
  • @JeremyJStarcher my answer includes some text. Could you explain to me how I could improve that text to give even more information about what the query is actually doing? Do you know that there is a pretty formally stated policy against link-only answers on StackOverflow, in case you're suggesting that an answer that points to a link is somehow a better answer than a posted and working piece of code right on the page? – Aaron Bertrand Sep 21 '12 at 22:32
  • @AaronBertrand -- Link-only answers to non-Stack Overflow are frowned on because if the original site vanishes, all record vanishes. Duplicate questions, on the other hand, get a 'link-only' answer. I tried to stay with SO-based answers when I could. Since you asked for comments on your answer, I'll put them there. – Jeremy J Starcher Sep 21 '12 at 22:37
  • @Jeremy An "answer" that just points to another question/answer on StackOverflow shouldn't be an answer. That's a comment (or a vote to close, if it's a true duplicate). – Aaron Bertrand Sep 21 '12 at 22:40