Author |
Topic |
MikeB
Constraint Violating Yak Guru
387 Posts |
Posted - 2005-10-13 : 11:10:13
|
I have a situation where the key for a table is alpha-numeric. I need to sort the recordset obtained by this key. The problem is that the sorting that is used by default reads from left to right and the following is a returned row set:C1C10C2C20 I need the recordset to return :C1C2C10C20 Now there is not definitive logic to the key naming convention used. So, how can I ensure that the numerical part of the key is sorted correctly?Mike B |
|
HCLollo
Starting Member
49 Posts |
Posted - 2005-10-13 : 11:26:31
|
Hi Mike,what do you mean with quote: no definitive key naming convention
?Any number of letters before any number of digits?HCL"If it works fine, then it doesn't have enough features" |
|
|
MikeB
Constraint Violating Yak Guru
387 Posts |
Posted - 2005-10-13 : 11:48:39
|
quote: Originally posted by HCLollo Hi Mike,what do you mean with quote: no definitive key naming convention
?Any number of letters before any number of digits?HCL"If it works fine, then it doesn't have enough features"
Pretty much, they could have any combination of characters before the numbers.C1CA1CA1ABC100BC1013-11-1etc... I know it is really screwy, but with the nature of the information for which the key is derived, this cannot be controlled in any sequence for the assignment of they key names.Mike B |
|
|
HCLollo
Starting Member
49 Posts |
Posted - 2005-10-13 : 12:02:34
|
quote: Originally posted by MikeB......CA1A......etc...
Also after? If not my first guess would be to iterate through thelength of the string unless you find a numeric value...something likethis:for i = 0 to Len(Column) test = Right(Column,Len(Column)-i) if IsNumber(test) then number = Right(Column,Len(Column)-i) alfanum = Left(Column,i) exit end ifnext Please note that this is VB-ish code, but I think it could be easilytranslatable in whatever you need/use...After that sort by number and/or alfanum .Hope this can help.HCL"If it works fine, then it doesn't have enough features" |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-10-13 : 12:15:58
|
SELECT MycolumnFROM MyTableORDER BY LEFT(MyColumn + '0000000000', 10) -- This should do it for up to 10 digits of AAAANNNN |
|
|
HCLollo
Starting Member
49 Posts |
Posted - 2005-10-13 : 12:22:43
|
Sam,shouldn't your select returnC1C10C2C20 instead ofC1C2C10C20 ?I mean, if I'm not wrong:LEFT('C1'+'0000000000',10) andLEFT('C10'+'0000000000',10) both should yieldC100000000 EDIT:maybeSELECT MycolumnFROM MyTableORDER BY LEFT(MyColumn + '0000000000', Len(Mycolumn)+10) could work? No, it wouldn't...ok, the night is on me, time to go back to life! HCL"If it works fine, then it doesn't have enough features" |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-10-13 : 12:44:02
|
Mike -- if you can change your design, spliting the numeric portion from the alpha portion will make things much easier and more efficient. Then you just concatenate them together as needed in any query. |
|
|
MikeB
Constraint Violating Yak Guru
387 Posts |
Posted - 2005-10-13 : 13:16:27
|
quote: Originally posted by jsmith8858 Mike -- if you can change your design, spliting the numeric portion from the alpha portion will make things much easier and more efficient. Then you just concatenate them together as needed in any query.
Well, I found that ORDER BY REVERSE(ID) works to a point. Each row item is categorized so as long as the users stick to the same notation (which they say they are going to) then the REVERS(str) function works fine. The only problem, like I stated, is if the notation isn't the same.[code]PL1, PL10, PL2, 3-2would sort toPL1PL23-2PL10[/CODE]So Reversing the id before sorting would work, as long as the key naming convention remains consistent. Note, that 3-2 is a different item type then the PL# items, so when retrieving the records by type, the 3-2 would not be returned and the items would fall into the correct order.I cannot change the design unfortunately, because my system will encompass data from a previous system with thousands of records already existing. An asking them to rename the keys is out of the question.Thanks for everyones input!Mike B |
|
|
MikeB
Constraint Violating Yak Guru
387 Posts |
Posted - 2005-10-18 : 14:08:53
|
Well, I see that the REVERSE didn't work like I expected so I had to revisit the sorting and come up with the following solution. Probably not the best, but it was all I could come up with.What I did was converted the alpha numeric string into its ASCII equivalent using the following function:CREATE FUNCTION fxGetAsciiRepresentation(@cString char(15))RETURNS integer AS BEGIN DECLARE @iLength int, @iRep int, @cTemp char(30), @cChar char(1), @iTemp int, @iPosition intSELECT @iLength = LEN(RTRIM(@cString)), @iPosition = 1WHILE (@iPosition <= @iLength) BEGIN SET @cChar = RIGHT(LEFT(RTRIM(@cString), @iPosition), 1) SET @iTemp = ASCII(@cChar) SET @cTemp = CASE WHEN @iPosition = 1 THEN CAST(@iTemp AS CHAR) ELSE RTRIM(@cTemp) + CAST(@iTemp AS VARCHAR) END SET @iPosition = @iPosition + 1 ENDSET @iRep = CAST(@cTemp AS INTEGER)RETURN @iRepEND I then call sort my recordsets with the following:sort by dbo.fxGetAsciiRepresentation(COLUMM) This works pretty good. Any thoughts on something better. I know as posted previously that a design change would be best, but at this point it is not possible.Mike B |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-10-19 : 01:00:21
|
If your data has fixed length then this will workSelect Columns from yourTable order by Len(col),colBut As you have varied length of data, you need to use two columns as suggestedMadhivananFailing to plan is Planning to fail |
|
|
|