Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Sorting

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:


C1
C10
C2
C20


I need the recordset to return :

C1
C2
C10
C20


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"
Go to Top of Page

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.

C1
CA1
CA1A
BC100
BC101
3-1
1-1
etc...

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
Go to Top of Page

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 the
length of the string unless you find a numeric value...something like
this:

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 if
next


Please note that this is VB-ish code, but I think it could be easily
translatable 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"
Go to Top of Page

SamC
White Water Yakist

3467 Posts

Posted - 2005-10-13 : 12:15:58
SELECT Mycolumn
FROM MyTable
ORDER BY LEFT(MyColumn + '0000000000', 10) -- This should do it for up to 10 digits of AAAANNNN
Go to Top of Page

HCLollo
Starting Member

49 Posts

Posted - 2005-10-13 : 12:22:43
Sam,
shouldn't your select return

C1
C10
C2
C20

instead of

C1
C2
C10
C20

?

I mean, if I'm not wrong:

LEFT('C1'+'0000000000',10)

and

LEFT('C10'+'0000000000',10)

both should yield

C100000000


EDIT:
maybe


SELECT Mycolumn
FROM MyTable
ORDER 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"
Go to Top of Page

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.
Go to Top of Page

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-2

would sort to
PL1
PL2
3-2
PL10
[/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
Go to Top of Page

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 int
SELECT @iLength = LEN(RTRIM(@cString)), @iPosition = 1

WHILE (@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
END

SET @iRep = CAST(@cTemp AS INTEGER)
RETURN @iRep
END



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
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-19 : 01:00:21
If your data has fixed length then this will work

Select Columns from yourTable order by Len(col),col

But As you have varied length of data, you need to use two columns as suggested


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -