Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

My BiPolar Users aka Sort a AlphaNumeric Field as if its Numeric Expand / Collapse
Author
Message
Posted Tuesday, January 15, 2013 10:54 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:34 PM
Points: 23,302, Visits: 32,056
Lynn Pettis (1/15/2013)
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.


Except, I don't think it works.


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 BipolarNumbers
FROM SampleData
ORDER 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 BipolarNumbers
FROM SampleData
ORDER 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)


This is what I get as a result:

BipolarNumbers
1
11
12
1003
11342
11342-B
11342-A
100231-A



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1407601
Posted Tuesday, January 15, 2013 10:57 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:59 PM
Points: 3,631, Visits: 5,280
Lynn Pettis (1/15/2013)
Lynn Pettis (1/15/2013)
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.


Except, I don't think it works.


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 BipolarNumbers
FROM SampleData
ORDER 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 BipolarNumbers
FROM SampleData
ORDER 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)


This is what I get as a result:

BipolarNumbers
1
11
12
1003
11342
11342-B
11342-A
100231-A


Picky! Picky!

How about something simpler then?

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 BipolarNumbers
FROM SampleData
ORDER BY CAST(LEFT(BipolarNumbers
,CASE PATINDEX('%[^0-9]%', BipolarNumbers)
WHEN 0 THEN LEN(BipolarNumbers)
ELSE PATINDEX('%[^0-9]%', BipolarNumbers) - 1 END) AS INT)
,BipolarNumbers





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1407602
Posted Tuesday, January 15, 2013 11:01 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:34 PM
Points: 23,302, Visits: 32,056
Is it wrong to be picky when it's wrong??

Still, I should have caught the simpler method. Good work!



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1407604
Posted Tuesday, January 15, 2013 11:09 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:59 PM
Points: 3,631, Visits: 5,280
Lynn Pettis (1/15/2013)
Is it wrong to be picky when it's wrong??

Still, I should have caught the simpler method. Good work!


Of course not. That's the good thing about such a huge community forum. Everybody keeps you honest.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1407611
Posted Wednesday, January 16, 2013 6:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, July 13, 2014 11:51 AM
Points: 115, Visits: 223
Well 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 (User

And 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.
Post #1407818
Posted Wednesday, January 16, 2013 7:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:48 AM
Points: 7,100, Visits: 6,927
or...

ORDER BY RIGHT(SPACE(20)+BipolarNumbers,20) ASC




Far away is close at hand in the images of elsewhere.

Anon.

Post #1407827
Posted Wednesday, January 16, 2013 7:58 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:16 AM
Points: 1,946, Visits: 3,210
David Burrows (1/16/2013)
or...

ORDER BY RIGHT(SPACE(20)+BipolarNumbers,20) ASC



I was thinking something like that. Didn't get as far as actually writing (or testing) it though
Post #1407845
Posted Wednesday, January 16, 2013 5:28 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:59 PM
Points: 3,631, Visits: 5,280
David Burrows (1/16/2013)
or...

ORDER BY RIGHT(SPACE(20)+BipolarNumbers,20) ASC



+1



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1408109
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse