[thelist] Sorting with SQL

[rudy]

> I've not had a chance to explore the finer details 
> of using SUBSTRING and CHARINDEX yet. 

mmmm, i love string functions

here ya go, rob

create table strings
 ( string  varchar(20)  not null )
 
insert into strings values ( 'ABC 1-2' )
insert into strings values ( 'ABC 3-6' )
insert into strings values ( 'ABC 4-5' )
insert into strings values ( 'ABC 10-3' )
insert into strings values ( 'ABC 2' )
insert into strings values ( 'ABC 22' )
insert into strings values ( 'ABC 222' )
insert into strings values ( 'ABC 2-2-2' )
insert into strings values ( 'ABC 2-22-222' )
insert into strings values ( 'ABCXYZ 937' )
insert into strings values ( 'ABCXYZ 937-eh' )
insert into strings values ( 'ABC' )
insert into strings values ( 'AB' )
insert into strings values ( 'A' )

select string
     , cast(
         case when charindex(' ',string) = 0
              then '0'
              else substring(
                     string
                   , charindex(' ',string)+1
                   , case when charindex('-',string) = 0
                          then len(string)
                             - charindex(' ',string)
                          else charindex('-',string)
                             - charindex(' ',string) - 1
                     end     )
        end as smallint  ) as  numericsortvalue
  from strings  
order 
    by cast(
         case when charindex(' ',string) = 0
              then '0'
              else substring(
                     string
                   , charindex(' ',string)+1
                   , case when charindex('-',string) = 0
                          then len(string)
                             - charindex(' ',string)
                          else charindex('-',string)
                             - charindex(' ',string) - 1
                     end     )
        end as smallint  )      

string               numericsortvalue 
-------------------- ---------------- 
ABC                  0
AB                   0
A                    0
ABC 1-2              1
ABC 2                2
ABC 2-2-2            2
ABC 2-22-222         2
ABC 3-6              3
ABC 4-5              4
ABC 10-3             10
ABC 22               22
ABC 222              222
ABCXYZ 937           937
ABCXYZ 937-eh        937

        

by the way, i totally endorse what ken suggested --
alter the table and add a separate permanent column for sorting


rudy

p.s. 

20 years database programming, ken?  whoa

what was your first database?  mine was idms in 1975...