>Dim strCode 'as string
>
>strCode = "J2G 9H8" 'can postal code
>
>in my Firms table :
>
>FirmID FirmName PostalCode
>1 FirmA J4X 4Z2 (1 chr)
>2 FirmB J2K 3R5 (2 chrs)
>3 FirmC J2G 9B7 (4 chrs + space)
>4 FirmD J2G 3T7 (3 chrs + space)
>
>
>So I need a recordset order by number of left chrs like strCode : FirmC, FirmD, FirmB, FirmA
>
>
>Is this clear ? Don't think so !
>
>
>Simon

DECLARE @StrCode char(6)
SET char(6) = 'J2G 9H8'
SELECT FirmName, PostalCode
  FROM mytable 
  WHERE PostalCode = LEFT(@StrCode,1)
  ORDER BY
  CASE
    WHEN PostalCode = @StrCode THEN 6
    WHEN PostalCode = LEFT(@StrCode,5) THEN 5
    WHEN PostalCode = LEFT(@StrCode,4) THEN 4
    WHEN PostalCode = LEFT(@StrCode,3) THEN 3
    WHEN PostalCode = LEFT(@StrCode,2) THEN 2
    WHEN PostalCode = LEFT(@StrCode,1) THEN 1
    ELSE 0
  END DESC