List:General Discussion« Previous MessageNext Message »
From:Erich Beyrent Date:March 23 2004 7:57pm
Subject:RE: Sorting with an alpha numeric field.
View as plain text  
> I have something like A001C, B689, B1001 etc...

I had to do something like this, and someone suggested the following:

order by 
    case 
      when substring(YourField,1,1) between '0' and '9' 
      then ''
      when substring(YourField,2,1) between '0' and '9' 
      then left(YourField,1)
      when substring(YourField,3,1) between '0' and '9' 
      then left(YourField,2)
      ...
      else 'ZZZZZ'
    end
, cast(
    case 
      when substring(YourField,1,1) between '0' and '9' 
      then substring(YourField,1)
      when substring(YourField,2,1) between '0' and '9' 
      then concat('0',substring(YourField,2))
      when substring(YourField,3,1) between '0' and '9' 
      then concat('00',substring(YourField,3))
      ...
      else 99999
    end
  as integer)  


I never was able to get it to work the way I wanted though...  Perhaps
it will trigger some ideas from the talented people on this list.

HTH

-Erich-


Thread
Sorting with an alpha numeric field.Jacque Scott23 Mar
  • Re: Sorting with an alpha numeric field.Dan Nelson23 Mar
  • Re: Sorting with an alpha numeric field.Garth Webb23 Mar
Re: Sorting with an alpha numeric field.Jacque Scott23 Mar
  • Re: Sorting with an alpha numeric field.Dan Nelson23 Mar
    • RE: Sorting with an alpha numeric field.Erich Beyrent23 Mar
Re: Sorting with an alpha numeric field.Jacque Scott23 Mar