[thelist] Sorting with SQL

rudy rudy937 at rogers.com
Thu Sep 25 12:24:56 CDT 2003


> The known patterns are:
> 
>   LLL N-N
>   LLL NN-N
>   LL N-N
>   NLNN
>   LLLLLL
>   NNNN
> 
> Would you then just extend the case statements? 

yes

what the revised code below does is check for the blank, 
and if there is one, it goes ahead with pulling out the 
numerics based on the position of the dash (and note that 
it *will* crash if it finds LLL NL-N or LLL LN-N)

but if there is no blank, it now checks to see if the 
entire string contains a non-numeric character

if the entire string is all numerics (NNNN) then that's 
the sort key used, but if there is a non-numeric, it pulls
out only the leading numerics

pretty neat, eh?

please don't come back and revise this once again, because 
i'm afraid i'll have to say YOYO -- you're on your own

;o)


rudy


delete from strings

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 ( 'OK 1-1' )
insert into strings values ( 'OK 9-9' )

insert into strings values ( '2A10' )
insert into strings values ( '9X99' )
insert into strings values ( 'LETTERS' )
insert into strings values ( 'NUMBERS' )
insert into strings values ( '0937' )
insert into strings values ( '9370' )

select string
     , cast(
         case when charindex(' ',string) = 0
              then case when string like '%[^0-9]%'
                        then 
 case when patindex('%[^0-9]%',string) > 1
      then substring(string,1
                    ,patindex('%[^0-9]%',string)-1)
      else '0' end
                        else string end 
              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 case when string like '%[^0-9]%'
                        then 
 case when patindex('%[^0-9]%',string) > 1
      then substring(string,1
                    ,patindex('%[^0-9]%',string)-1)
      else '0' end
                        else string end 
              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                    

string               numericsortvalue 
-------------------- ---------------- 
LETTERS              0
NUMBERS              0
ABC 1-2              1
OK 1-1               1
2A10                 2
ABC 3-6              3
ABC 4-5              4
9X99                 9
OK 9-9               9
ABC 10-3             10
0937                 937
9370                 9370




More information about the thelist mailing list