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 4:30 PM
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
My 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:

1
100231-A
1003
11
11342
11342-A
12

They want
1
11
12
1003
11342
11342-A
100231-a

I'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?
Post #1407516
Posted Tuesday, January 15, 2013 4:45 PM
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 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.html
Post #1407518
Posted Tuesday, January 15, 2013 6:02 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: Yesterday @ 11:12 PM
Points: 3,616, Visits: 5,230
Are 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.

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)





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 #1407531
Posted Tuesday, January 15, 2013 7:56 PM
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
It 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.
Post #1407550
Posted Tuesday, January 15, 2013 8:00 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: Yesterday @ 11:12 PM
Points: 3,616, Visits: 5,230
david.holley (1/15/2013)
It does place anything starting with a letter at the end which is consistent with another application that we have.


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.



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 #1407551
Posted Tuesday, January 15, 2013 8:02 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: Yesterday @ 11:12 PM
Points: 3,616, Visits: 5,230
BTW. You should be familiar with the issues of using ISNUMERIC() if you're going to use it.

http://www.sqlservercentral.com/articles/IsNumeric/71512/



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 #1407552
Posted Tuesday, January 15, 2013 8:30 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 12:32 PM
Points: 23,059, Visits: 31,583
Please compare the following two pieces of code:


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


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?



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 #1407555
Posted Tuesday, January 15, 2013 10:29 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: Yesterday @ 11:12 PM
Points: 3,616, Visits: 5,230
Lynn Pettis (1/15/2013)
Please compare the following two pieces of code:


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


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?


It appears you've identified a flaw in my sort order! Shame on me!

This might fix it:

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)
,PATINDEX('%[^0-9]%', 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 #1407594
Posted Tuesday, January 15, 2013 10:46 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 12:32 PM
Points: 23,059, Visits: 31,583
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.



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 #1407598
Posted Tuesday, January 15, 2013 10:52 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: Yesterday @ 11:12 PM
Points: 3,616, Visits: 5,230
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.


Shoot man! If not for your eagle eye the mistake could have easily gone unnoticed in the first place.



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 #1407600
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse