January 15, 2013 at 4:30 pm
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?
January 15, 2013 at 4:45 pm
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
January 15, 2013 at 6:02 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 15, 2013 at 7:56 pm
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.
January 15, 2013 at 8:00 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 15, 2013 at 8:02 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 15, 2013 at 8:30 pm
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?
January 15, 2013 at 10:29 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 15, 2013 at 10:46 pm
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.
January 15, 2013 at 10:52 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 15, 2013 at 10:54 pm
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
January 15, 2013 at 10:57 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 15, 2013 at 11:01 pm
Is it wrong to be picky when it's wrong?? :unsure:
Still, I should have caught the simpler method. Good work!
January 15, 2013 at 11:09 pm
Lynn Pettis (1/15/2013)
Is it wrong to be picky when it's wrong?? :unsure: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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 16, 2013 at 6:56 am
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.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply