SO 39069889 - Apartment sorting problem, Sql Server

Language: Layout:

with v(apt) as (
    select '1' union all select '10' union all select '101' union all select '101-A' union all
    select '1000' union all select '200' union all select '200A' union all select '200-A' union all
    select '2000' union all select 'C' union all select 'D' union all select 'E' union all
    select 'E10' union all select 'E100' union all select 'E-100' union all
    select 'E101' union all select 'E-101' union all select 'E200' union all
    select 'E-200' union all select 'E1000' union all select 'E-1001' union all
    select 'F'
)
select apt,
    case
         when patindex('%[0-9][A-Z-]%', apt) > 0 then cast(substring(apt, 1, patindex('%[0-9][A-Z-]%', apt)) as int)
         when patindex('[0-9]%', apt) > 0 then cast(apt as int)
         else 99999
    end as numeric_prefix,
    case
         when patindex('%[A-Z][0-9-]%', apt) > 0 then left(apt, patindex('%[A-Z][0-9-]%', apt))
         when patindex('[A-Z]', apt) = 1 then apt
         else ''
    end as char_prefix,
    case
         when patindex('%[A-Z-][0-9]%', apt) > 0 then cast(substring(apt, patindex('%[A-Z-][0-9]%', apt) + 1, 10) as int)
         else 0
    end as numeric_suffix,
    replace(apt, '-', '') as stripped_hyphen,
    case when charindex('-', apt) = 0 then 0 else 1 end as sort_hyphen_last
from v
order by
    case
         when patindex('%[0-9][A-Z-]%', apt) > 0 then cast(substring(apt, 1, patindex('%[0-9][A-Z-]%', apt)) as int)
         when patindex('[0-9]%', apt) > 0 then cast(apt as int)
         else 999999
    end,
    case
         when patindex('%[A-Z][0-9-]%', apt) > 0 then left(apt, patindex('%[A-Z][0-9-]%', apt))
         when patindex('[A-Z]', apt) = 1 then apt
         else ''
    end,
    case
         when patindex('%[A-Z-][0-9]%', apt) > 0 then cast(substring(apt, patindex('%[A-Z-][0-9]%', apt) + 1, 10) as int)
         else 0
    end,
    case when charindex('-', apt) = 0 then 0 else 1 end;

when patindex('%[A-Z][0-9-]%', apt) > 0
         then right('>>>>>' + left(apt, patindex('%[A-Z][0-9-]%', apt)), 5) +
              right('*****' + replace(substring(apt, patindex('%[A-Z][0-9-]%', apt) + 1, 10), '-', ''), 5)

when patindex('[0-9]%', apt) = 1 then right('*****' + apt, 5) + '>>>>>'
         when patindex('[A-Z]%', apt) = 1 then right('>>>>>' + apt, 5) + '*****'
         else '>>>>>*****'
    end +
    case when charindex('-', apt) = 0 then '' else '-' end /* collate Latin1_General_BIN */ as sort_string 
from v
order by sort_string

View schema
Execution time: 0,02 sec, rows selected: 22, rows affected: 0, absolute service time: 0,17 sec