[dba-SQLServer] sorting problem

Hi Arthur:

Thanks for your input.  During the time of the original post, I wasn't 
really to sure what all the rules were.  But I knew one sorting rule for 
certain, which was "sort letters before numbers".

a10x does not come ahead of a10.  Not too sure how to articulate it 
accurately; the closest I can explain it is "the characters are read from 
left to right".  If I create two text files in my OS (I am using win2k) 
called a10.txt and a10x.txt and sort the files in ascending order, a10.txt 
is shown first.  I struggled with this rule because in my mind I kept 
thinking that "no character" takes greater precedence than "the presence of 
a character".

Thanks for your solution but it is not what I am looking for.  In a 
nutshell, the InnerNumeric_fn function obtains the first complete number in 
a string?  I like the idea of using a function to manufacture a sorting key. 
  Your code uses little cpu cycles and reads.  I can say that because I 
tried re-writing your function without using a "while" condition and 
compared the two and this is what I got.

create FUNCTION dbo.InnerNumeric_fn_1(@Source Varchar(10)) RETURNS INT
AS
   BEGIN
       declare @Result varchar(9)
         declare @tempTable TABLE(THE_ID TINYINT NOT NULL PRIMARY KEY 
IDENTITY(1,1), THE_VALUE CHAR(1))
         INSERT INTO @tempTable VALUES('A');
         INSERT INTO @tempTable VALUES('A');
         INSERT INTO @tempTable SELECT i.the_value from @tempTable i, 
@tempTable ii, @tempTable iii
         SELECT @Result = ISNULL(@Result,'') +  CASE WHEN THE_ID > 1 AND 
isnumeric(substring(@Source,THE_ID,1)) = 1 THEN substring(@Source,THE_ID,1) 
ELSE '' END FROM @tempTable;
         RETURN CAST(@Result AS INT)
   END
go

However, this solution is great for the sample values I posted but it is not 
exactly what I am looking for because even though it can handle any number 
of digits, I am unable to use it as a sorting solution since it cannot 
handle two sets of digits.  For example:

a45x1x
a45xax
a45x2x

Sincerely,

Billy

>From: Arthur Fuller <artful at rogers.com>
>Reply-To: dba-sqlserver at databaseadvisors.com
>To: dba-sqlserver at databaseadvisors.com
>Subject: Re: [dba-SQLServer] sorting problem
>Date: Sun, 31 Oct 2004 15:44:53 -0500
>
>I should have added to the preceding message that all this is necessary 
>only if the 6 values orginally supplied were just a sample. If they are 
>actually all the values that the column can contain, it's much simpler just 
>to create a new two-column table containing the values and their desired 
>order, expressed as an int. Then you could join said table to the table of 
>interest and order by the new table's second column.
>
>The code I provided in the previous message can handle any number of digits 
>and any number of trailing characters.
>
>Arthur
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>