[dba-SQLServer] sorting problem

-----Original Message-----

>>From: dba-sqlserver-bounces at databaseadvisors.com 
>>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf 
>>Of Billy Pang
>>Sent: Friday, October 29, 2004 8:35 PM
>>To: dba-SQLServer at databaseadvisors.com
>>Subject: [dba-SQLServer] sorting problem
>>
>>
>>Hello:
>>
>>I have a table that contains one column with the following six values:
>>
>>a1
>>a2
>>a21
>>a10x
>>a2a
>>a10
>>
>>Currently if that column is sorted, it is sorted this way:
>>
>>a1
>>a10
>>a10x
>>a2
>>a21
>>a2a
>>
>>But I want it to be sorted this way:
>>
>>a1
>>a2
>>a2a
>>a10
>>a10x
>>a21
>>
>>(note: a2a comes before a10, a21 goes after a10x, etc...ergo, 
>>letters come 
>>before numbers, contrary to their ascii values)
>>
>>Is it possible or is it a pipe dream?  Below is copy of code.
>>
>>Thanks in advance,
>>Billy
>>
>>    
>>
I have played with this for a while. I kept thinking that this should 
not be difficult, but either I'm having a senior Sunday or it simply is 
difficult. Also, I finally realized that I should look at your sample 
data more than your description of the rules, since the rules are 
oversimplified. I am having trouble deciding what your actual rules are, 
but clearly your description is insufficient... at least for this old 
coder. If letters come before numbers, why isn't a10x ahead of a10? It's 
looking to me as if your actual rules are:
1. ignore the first letter
2. grab the numeric value following the first letter as your first sort 
criterion
3. anything following that numeric value is the second sort order (i.e. 
10, 10x, 10y)

Here, I believe, is your answer: create the following function (which is 
not optimized, it's written in my usual style with lots of variables so 
I can check everything as I go) first:

<code>
ALTER FUNCTION dbo.InnerNumeric_fn
(
    @Source Varchar(20) = 'a20x'
)
RETURNS integer
AS
    BEGIN
        declare @Result varchar(10)
        declare @i int
        declare @j int
        declare @c char(1)
        declare @flag int
        set @j = len(@Source)
        set @i = 2
        set @Result = ''
        while @i <= @j
            begin
            set @c = substring(@Source, at i,1)
            set @flag = isnumeric(@c)
            if (@flag = 1)
                begin
                    set @Result = @Result + @c
                    set @i = @i+1
                end
            else
                break
            end
   
        RETURN CAST(@Result AS INT)
    END
</code>

Use it in your query as follows:

<code>
select *,dbo.innernumeric_fn(the_value) from the_table
order by dbo.innernumeric_fn(the_value)
</code>

This prints the following set, which appears to be what you want:

a1    1
a2    2
a2a    2
a10    10
a10x    10
a21    21

HTH,
Arthur

P.S.
I'll be using this in one of my forthcoming SQL Tips columns. I might as 
well get somebody to pay me for writing it. LOL.