SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Sorting
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

MikeB
Constraint Violating Yak Guru

Canada
387 Posts

Posted - 10/13/2005 :  11:10:13  Show Profile
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

Italy
49 Posts

Posted - 10/13/2005 :  11:26:31  Show Profile
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

Canada
387 Posts

Posted - 10/13/2005 :  11:48:39  Show Profile
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

Italy
49 Posts

Posted - 10/13/2005 :  12:02:34  Show Profile
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

USA
3460 Posts

Posted - 10/13/2005 :  12:15:58  Show Profile
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

Italy
49 Posts

Posted - 10/13/2005 :  12:22:43  Show Profile
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"

Edited by - HCLollo on 10/13/2005 12:36:25
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 10/13/2005 :  12:44:02  Show Profile  Visit jsmith8858's Homepage
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

Canada
387 Posts

Posted - 10/13/2005 :  13:16:27  Show Profile
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.


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

Canada
387 Posts

Posted - 10/18/2005 :  14:08:53  Show Profile
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

India
22744 Posts

Posted - 10/19/2005 :  01:00:21  Show Profile  Send madhivanan a Yahoo! Message
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
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000