[dba-SQLServer] sorting problem

Billy Pang wrote:

> 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

Fine! Fine! Fine! In future, I suggest that you provide your third-world 
developers with an adequeate data set. LOL. I shall go back to the salt 
mines and toil endlessly until I have a solution. But you still haven't 
made the rules quite clear, if I may say so, Sir.

Given strings x##x#x and xx###xx#xx, please define the desired sort order.

I think I can do this by sequential calls to the func I wrote, but I 
could be wrong. I need more evidence to determine how to revise my 
inadequate contribution thus far.

A.