sorting an alphanumeric field
-
All,
I found a few posts that I thought applied to what I am trying to do. So, I here is the code and sample data I working with:
DECLARE @TestData TABLE (StringValue VARCHAR(20))
INSERT INTO @TestData
SELECT '10' UNION ALL
SELECT '22' UNION ALL
SELECT '1A' UNION ALL
SELECT '1' UNION ALL
SELECT 'S1' UNION ALL
SELECT '3B' UNION ALL
SELECT '4' UNION ALL
SELECT '7'
SELECT StringValue
FROM @TestData
ORDER BY CASE WHEN ISNUMERIC(StringValue) = 1 THEN CAST(StringValue AS INT) END ASC,
CASE WHEN ISNUMERIC(StringValue) = 0 THEN StringValue END ASC
The sort order i get back is:
1A
3B
S1
1
4
7
10
22
What I want is
1
1A
3B
4
7
10
22
S1
Basically, I want the numeric characters sorted first followed by the alpha characters.
-
Normalize your table.
_____________
Code for TallyGenerator -
How would that help?
Try this
SELECT
StringValue
FROM (
SELECT StringValue,
CASE
WHEN StringValue LIKE '[0-9]%' THEN 0
ELSE 1
END AS Alpha,
CASE
WHEN PATINDEX('%[^0-9]%', StringValue) = 0 THEN StringValue
ELSE LEFT(StringValue, PATINDEX('%[^0-9]%', StringValue) - 1)
END AS Value
FROM @TestData
) AS d
ORDER BY Alpha,
LEN(Value),
Value,
StringValue
N 56°04'39.16"
E 12°55'05.25" -
Then what did you do?
You split original column into 2 (normalized the data) and built derived table with normalized data on fly.
No indexes, no statistics.
Worst possible approach.
Populate @TestData with 100k rows and see how long will it take to do this ordering.
_____________
Code for TallyGenerator -
Please demonstrate how you would solve this then!
You just can't tell people to "Normalize your table" when you have absolutely no idea what the data represents.
I would have guessed that they are part of street addresses, but 'S1' is present.
N 56°04'39.16"
E 12°55'05.25" -
By the way it requested to be ordered I can conclude it's 2 concatenated values.
Saving concatenated strings in tables is an error which will cause (already causes) problems in retrieving data.
_____________
Code for TallyGenerator -
How can you tell?
Are you sure they are not serial numbers? Encrypted values?
We do not simply know and have not enough information to do that assumption.
You are similar to President Putin, who not long ago said in a television interview "Russia is the only true democratic country in the world".
You are always convinced that you are never wrong.
N 56°04'39.16"
E 12°55'05.25" -
I can tell.
It's ordered to sort by some kind of group (integer value at the beginning) and then by item (second part).
And you cannot explain why to sort serial numbers like that.
So, it's not serial numbers.
Any ideas not matching my?
P.S. I see you're very educated person. You know the name "Putin".
_____________
Code for TallyGenerator -
> You are always convinced that you are never wrong.
Are you sure you are right?
_____________
Code for TallyGenerator -
I always wanted to know how fast that method ran
--================================================================================================= -- Prepare a test table --=================================================================================================
--===== If the test table exists, drop IF OBJECT_ID('TempDB.dbo.#TestData') IS NOT NULL DROP TABLE #TestData GO --===== Create and populate the test table with 100000 rows SELECT TOP 100000 RowNum = IDENTITY(INT,1,1), StringValue = CAST(NULL AS VARCHAR(10)) INTO #TestData FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2 --Lack of join criteria makes this a CROSS-JOIN--===== Add a primary key ALTER TABLE dbo.#TestData ADD PRIMARY KEY CLUSTERED (RowNum)
--===== Populate the table with a bunch of random data in the forms of N, A, NA, and AN UPDATE dbo.#TestData SET StringValue = CASE WHEN RowNum % 10 = 0 THEN CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*9+48)) --Digit WHEN RowNum % 5 = 0 THEN CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65)) --Letter WHEN RowNum % 10 < 5 THEN CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*9+48)) --Digit + CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65)) --Letter ELSE CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65)) --Letter + CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*9+48)) --Digit END
--===== Take a look at the test data just because SELECT * FROM #TestData
--================================================================================================= -- Test the given solution for duration --================================================================================================= --===== Declare a timer variable and start the timer DECLARE @StartTime DATETIME SET @StartTime = GETDATE()
--===== Run the example code that was posted SELECT StringValue FROM ( SELECT StringValue, CASE WHEN StringValue LIKE '[0-9]%' THEN 0 ELSE 1 END AS Alpha, CASE WHEN PATINDEX('%[^0-9]%', StringValue) = 0 THEN StringValue ELSE LEFT(StringValue, PATINDEX('%[^0-9]%', StringValue) - 1) END AS Value FROM #TestData ) AS d ORDER BY Alpha, LEN(Value), Value, StringValue--===== Test complete... display the duration PRINT CONVERT(CHAR(12),GETDATE()-@StartTime,114) + ' Duration (hh:mm:ss:mil)'
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Bill,
Thanks for posting code that actually creates some data... usually saves a lot of time for respondents and we appreciated it a LOT!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Is it good or bad result?
Let's check:
SELECT StringValue,
CASE WHEN StringValue LIKE '[0-9]%' THEN 0 ELSE 1 END AS Alpha,
CONVERT(int, CASE
WHEN PATINDEX('%[^0-9]%', StringValue) = 0 THEN StringValue
ELSE LEFT(StringValue, PATINDEX('%[^0-9]%', StringValue) - 1)
END) AS Value
INTO #TestData2
FROM #TestData
DECLARE @StartTime DATETIME
SET @StartTime = GETDATE()
SELECT StringValue
FROM #TestData2
ORDER BY Alpha,
Value,
StringValue
PRINT CONVERT(CHAR(12),GETDATE()-@StartTime,114) + ' Duration (hh:mm:ss:mil)'
-----------------------
Well, 2.5 times faster on my computer.
What about yours?
_____________
Code for TallyGenerator -
No, I am not.
I am just responding to a need that where posted here. I can't tell why he wants to sort the numbers the way he wanted. Maybe it is a business rule? Maybe it is easier to read and manually search from a printed list?
N 56°04'39.16"
E 12°55'05.25" -
Why didn't you time your complete code posted 6/23/2007 12:12:00 AM?
You only timed the last select, not the "normalization part". Why?
And I can't see somewhere that you actually had made a workable solution of your own.
You are just riding on my original suggestion!
N 56°04'39.16"
E 12°55'05.25" -
I agree with Jeff. Excellent work Bill with both creating a sample set, a clear definition of what you want and also (thumbs up) a complete resultset how you wanted the final result to be!
Kudos to you!
N 56°04'39.16"
E 12°55'05.25"
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply