InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 12:04:33 GMT20http://www.sqlservercentral.com/Forums/Topic1407516-391-1.aspx[quote][b]David Burrows (1/16/2013)[/b][hr]or...[code="sql"]ORDER BY RIGHT(SPACE(20)+BipolarNumbers,20) ASC[/code][/quote]+1Wed, 16 Jan 2013 17:28:54 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1407516-391-1.aspx[quote][b]David Burrows (1/16/2013)[/b][hr]or...[code="sql"]ORDER BY RIGHT(SPACE(20)+BipolarNumbers,20) ASC[/code][/quote]I was thinking something like that. Didn't get as far as actually writing (or testing) it though :-)Wed, 16 Jan 2013 07:58:22 GMTGazarethhttp://www.sqlservercentral.com/Forums/Topic1407516-391-1.aspxor...[code="sql"]ORDER BY RIGHT(SPACE(20)+BipolarNumbers,20) ASC[/code]Wed, 16 Jan 2013 07:16:23 GMTDavid Burrowshttp://www.sqlservercentral.com/Forums/Topic1407516-391-1.aspxWell dang it! I forgot that the whole reason for this is because sometimes they'll take a system generated numeric reference number and create their own using letters as in ...700234 (System)700234-A (User)700234-B (UserAnd thus the 'A' and 'B' needs to appear below the original. I saw the other posts and will see if something there will work. At least I know that my idea of using two RegEx's was a rather solid solution.Wed, 16 Jan 2013 06:56:00 GMTdavid.holleyhttp://www.sqlservercentral.com/Forums/Topic1407516-391-1.aspx[quote][b]Lynn Pettis (1/15/2013)[/b][hr]Is it wrong to be picky when it's wrong?? :unsure:Still, I should have caught the simpler method. Good work![/quote]Of course not. That's the good thing about such a huge community forum. Everybody keeps you honest. :-)Tue, 15 Jan 2013 23:09:34 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1407516-391-1.aspxIs it wrong to be picky when it's wrong?? :unsure:Still, I should have caught the simpler method. Good work!Tue, 15 Jan 2013 23:01:11 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1407516-391-1.aspx[quote][b]Lynn Pettis (1/15/2013)[/b][hr][quote][b]Lynn Pettis (1/15/2013)[/b][hr]Can't believe I missed such an easy solution. I guess that's what I get for making things harder than the need to be at first.[/quote]Except, I don't think it works.[code="sql"]WITH SampleData (BipolarNumbers) AS ( SELECT '1' UNION ALL SELECT '100231-A' UNION ALL SELECT '1003' UNION ALL SELECT '11' UNION ALL SELECT '11342' UNION ALL SELECT '11342-A' UNION ALL SELECT '12')SELECT BipolarNumbersFROM SampleDataORDER BY CAST(LEFT(BipolarNumbers ,CASE PATINDEX('%[^0-9]%', BipolarNumbers) WHEN 0 THEN LEN(BipolarNumbers) ELSE PATINDEX('%[^0-9]%', BipolarNumbers) - 1 END) AS INT);WITH SampleData (BipolarNumbers) AS ( SELECT '1' UNION ALL SELECT '100231-A' UNION ALL SELECT '1003' UNION ALL SELECT '11' UNION ALL SELECT '11342' UNION ALL SELECT '11342-A' UNION ALL SELECT '11342-B' UNION ALL SELECT '12')SELECT BipolarNumbersFROM SampleDataORDER BY CAST(LEFT(BipolarNumbers ,CASE PATINDEX('%[^0-9]%', BipolarNumbers) WHEN 0 THEN LEN(BipolarNumbers) ELSE PATINDEX('%[^0-9]%', BipolarNumbers) - 1 END) AS INT) ,PATINDEX('%[^0-9]%', BipolarNumbers)[/code]This is what I get as a result:BipolarNumbers1111210031134211342-B11342-A100231-A[/quote]Picky! Picky!How about something simpler then?[code="sql"]WITH SampleData (BipolarNumbers) AS ( SELECT '1' UNION ALL SELECT '100231-A' UNION ALL SELECT '1003' UNION ALL SELECT '11' UNION ALL SELECT '11342' UNION ALL SELECT '11342-A' UNION ALL SELECT '11342-B' UNION ALL SELECT '12')SELECT BipolarNumbersFROM SampleDataORDER BY CAST(LEFT(BipolarNumbers ,CASE PATINDEX('%[^0-9]%', BipolarNumbers) WHEN 0 THEN LEN(BipolarNumbers) ELSE PATINDEX('%[^0-9]%', BipolarNumbers) - 1 END) AS INT) ,BipolarNumbers[/code]Tue, 15 Jan 2013 22:57:49 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1407516-391-1.aspx[quote][b]Lynn Pettis (1/15/2013)[/b][hr]Can't believe I missed such an easy solution. I guess that's what I get for making things harder than the need to be at first.[/quote]Except, I don't think it works.[code="sql"]WITH SampleData (BipolarNumbers) AS ( SELECT '1' UNION ALL SELECT '100231-A' UNION ALL SELECT '1003' UNION ALL SELECT '11' UNION ALL SELECT '11342' UNION ALL SELECT '11342-A' UNION ALL SELECT '12')SELECT BipolarNumbersFROM SampleDataORDER BY CAST(LEFT(BipolarNumbers ,CASE PATINDEX('%[^0-9]%', BipolarNumbers) WHEN 0 THEN LEN(BipolarNumbers) ELSE PATINDEX('%[^0-9]%', BipolarNumbers) - 1 END) AS INT);WITH SampleData (BipolarNumbers) AS ( SELECT '1' UNION ALL SELECT '100231-A' UNION ALL SELECT '1003' UNION ALL SELECT '11' UNION ALL SELECT '11342' UNION ALL SELECT '11342-A' UNION ALL SELECT '11342-B' UNION ALL SELECT '12')SELECT BipolarNumbersFROM SampleDataORDER BY CAST(LEFT(BipolarNumbers ,CASE PATINDEX('%[^0-9]%', BipolarNumbers) WHEN 0 THEN LEN(BipolarNumbers) ELSE PATINDEX('%[^0-9]%', BipolarNumbers) - 1 END) AS INT) ,PATINDEX('%[^0-9]%', BipolarNumbers)[/code]This is what I get as a result:BipolarNumbers1111210031134211342-B11342-A100231-ATue, 15 Jan 2013 22:54:22 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1407516-391-1.aspx[quote][b]Lynn Pettis (1/15/2013)[/b][hr]Can't believe I missed such an easy solution. I guess that's what I get for making things harder than the need to be at first.[/quote]Shoot man! If not for your eagle eye the mistake could have easily gone unnoticed in the first place.Tue, 15 Jan 2013 22:52:53 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1407516-391-1.aspxCan't believe I missed such an easy solution. I guess that's what I get for making things harder than the need to be at first.Tue, 15 Jan 2013 22:46:37 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1407516-391-1.aspx[quote][b]Lynn Pettis (1/15/2013)[/b][hr]Please compare the following two pieces of code:[code="sql"]WITH SampleData (BipolarNumbers) AS ( SELECT '1' UNION ALL SELECT '100231-A' UNION ALL SELECT '1003' UNION ALL SELECT '11' UNION ALL SELECT '11342' UNION ALL SELECT '11342-A' UNION ALL SELECT '12')SELECT BipolarNumbersFROM SampleDataORDER BY CAST(LEFT(BipolarNumbers, CASE PATINDEX('%[^0-9]%', BipolarNumbers) WHEN 0 THEN LEN(BipolarNumbers) ELSE PATINDEX('%[^0-9]%', BipolarNumbers) - 1 END) AS INT), substring(BipolarNumbers,CASE PATINDEX('%[^0-9]%', BipolarNumbers) WHEN 0 THEN 0 ELSE PATINDEX('%[^0-9]%', BipolarNumbers) end, CASE PATINDEX('%[^0-9]%', BipolarNumbers) WHEN 0 THEN 0 ELSE (LEN(BipolarNumbers) - PATINDEX('%[^0-9]%', BipolarNumbers)) + 1 end);WITH SampleData (BipolarNumbers) AS ( SELECT '1' UNION ALL SELECT '100231-A' UNION ALL SELECT '1003' UNION ALL SELECT '11' UNION ALL SELECT '11342' UNION ALL SELECT '11342-A' UNION ALL SELECT '12')SELECT BipolarNumbersFROM SampleDataORDER BY CAST(LEFT(BipolarNumbers ,CASE PATINDEX('%[^0-9]%', BipolarNumbers) WHEN 0 THEN LEN(BipolarNumbers) ELSE PATINDEX('%[^0-9]%', BipolarNumbers) - 1 END) AS INT)[/code]The first is my modification to dwain's code and the second is his code unmodified. On my system they return two different result sets. Which one is correct?[/quote]It appears you've identified a flaw in my sort order! Shame on me!This might fix it:[code="sql"]WITH SampleData (BipolarNumbers) AS ( SELECT '1' UNION ALL SELECT '100231-A' UNION ALL SELECT '1003' UNION ALL SELECT '11' UNION ALL SELECT '11342' UNION ALL SELECT '11342-A' UNION ALL SELECT '12')SELECT BipolarNumbersFROM SampleDataORDER BY CAST(LEFT(BipolarNumbers ,CASE PATINDEX('%[^0-9]%', BipolarNumbers) WHEN 0 THEN LEN(BipolarNumbers) ELSE PATINDEX('%[^0-9]%', BipolarNumbers) - 1 END) AS INT) ,PATINDEX('%[^0-9]%', BipolarNumbers)[/code]Tue, 15 Jan 2013 22:29:44 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1407516-391-1.aspxPlease compare the following two pieces of code:[code="sql"]WITH SampleData (BipolarNumbers) AS ( SELECT '1' UNION ALL SELECT '100231-A' UNION ALL SELECT '1003' UNION ALL SELECT '11' UNION ALL SELECT '11342' UNION ALL SELECT '11342-A' UNION ALL SELECT '12')SELECT BipolarNumbersFROM SampleDataORDER BY CAST(LEFT(BipolarNumbers, CASE PATINDEX('%[^0-9]%', BipolarNumbers) WHEN 0 THEN LEN(BipolarNumbers) ELSE PATINDEX('%[^0-9]%', BipolarNumbers) - 1 END) AS INT), substring(BipolarNumbers,CASE PATINDEX('%[^0-9]%', BipolarNumbers) WHEN 0 THEN 0 ELSE PATINDEX('%[^0-9]%', BipolarNumbers) end, CASE PATINDEX('%[^0-9]%', BipolarNumbers) WHEN 0 THEN 0 ELSE (LEN(BipolarNumbers) - PATINDEX('%[^0-9]%', BipolarNumbers)) + 1 end);WITH SampleData (BipolarNumbers) AS ( SELECT '1' UNION ALL SELECT '100231-A' UNION ALL SELECT '1003' UNION ALL SELECT '11' UNION ALL SELECT '11342' UNION ALL SELECT '11342-A' UNION ALL SELECT '12')SELECT BipolarNumbersFROM SampleDataORDER BY CAST(LEFT(BipolarNumbers ,CASE PATINDEX('%[^0-9]%', BipolarNumbers) WHEN 0 THEN LEN(BipolarNumbers) ELSE PATINDEX('%[^0-9]%', BipolarNumbers) - 1 END) AS INT)[/code]The first is my modification to dwain's code and the second is his code unmodified. On my system they return two different result sets. Which one is correct?Tue, 15 Jan 2013 20:30:30 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1407516-391-1.aspxBTW. You should be familiar with the issues of using ISNUMERIC() if you're going to use it.[url]http://www.sqlservercentral.com/articles/IsNumeric/71512/[/url]Tue, 15 Jan 2013 20:02:53 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1407516-391-1.aspx[quote][b]david.holley (1/15/2013)[/b][hr]It does place anything starting with a letter at the end which is consistent with another application that we have.[/quote]Yup you missed mentioning that. :-)As long as it works for you but I thought it might also be sorting the ones that end with a character to the end.Tue, 15 Jan 2013 20:00:40 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1407516-391-1.aspxIt does place anything starting with a letter at the end which is consistent with another application that we have. I don't think that I specified that requirement. From what I can tell it seems to be working fine.Tue, 15 Jan 2013 19:56:08 GMTdavid.holleyhttp://www.sqlservercentral.com/Forums/Topic1407516-391-1.aspxAre you sure that link gives you what you need? I looked and it didn't look like the same problem.This should work as long as the leftmost characters in your string are digits.[code="sql"]WITH SampleData (BipolarNumbers) AS ( SELECT '1' UNION ALL SELECT '100231-A' UNION ALL SELECT '1003' UNION ALL SELECT '11' UNION ALL SELECT '11342' UNION ALL SELECT '11342-A' UNION ALL SELECT '12')SELECT BipolarNumbersFROM SampleDataORDER BY CAST(LEFT(BipolarNumbers ,CASE PATINDEX('%[^0-9]%', BipolarNumbers) WHEN 0 THEN LEN(BipolarNumbers) ELSE PATINDEX('%[^0-9]%', BipolarNumbers) - 1 END) AS INT)[/code]Tue, 15 Jan 2013 18:02:23 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1407516-391-1.aspxWell that was was simple enough. Now if anyone can recommend some really good antipsychotics that dissolve quickly in coffee...http://www.dbforums.com/microsoft-sql-server/1174759-sort-numerically-alphanumeric-column.htmlTue, 15 Jan 2013 16:45:51 GMTdavid.holleyhttp://www.sqlservercentral.com/Forums/Topic1407516-391-1.aspxMy users wanted a a field to be alphanumeric. That's fine. But they also want it to be sorted as if its numerically. That's not fine. So they're getting something like this:1100231-A1003111134211342-A12They want1111210031134211342-A100231-aI've figured out that I could provide them with the sorting that they're asking for if I were two use two RegEx's. One strips out all nonnumeric numbers, the other strips out all numbers. Its then just a matter of using an ORDER BY statement to sort based on the result of the first RegEx followed by the results of the second RegEx.My problem is that I can't figure out how to use a RegEx in SQL Server. I'm assuming of course that RegEx's would be the best route to go. Ideas? Alternatives? Medical Referrals?Tue, 15 Jan 2013 16:30:45 GMTdavid.holley