SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

with my_data as (
  select 'FORT BEND COUNTY MUD 2' as districtName union all
  select 'FORT BEND COUNTY MUD' union all
  select 'FORT BEND COUNTY MUD 23' union all
  select 'FORT BEND COUNTY MUD 138' union all
  select 'FORT BEND COUNTY MUD 256' union all
  select 'FORT BEND COUNTY MUD 250A' union all
  select 'FORT BEND COUNTY MUD 23 JT FAC' union all
  select 'FORT BEND COUNTY MUD 169 MASTER' union all
  select 'FORT BEND COUNTY MUD 214 INTERNAL' union all
  select 'FORT BEND COUNTY MUD 122 123 JT FAC' union all
  select 'FORT BEND COUNTY MUD 169 - INTERNAL' union all
  select 'FORT BEND COUNTY MUD 188 JT Sewer Plant' union all
  select 'FORT BEND COUNTY MUD 188 JT Water Plant' union all
  select 'FORT BEND COUNTY MUD 214 MASTER DISTRICT'
  )
select districtName
from my_data
ORDER BY LEFT(DistrictName, PATINDEX('%[0-9]%', DistrictName + '0') - 1), 
 cast(SUBSTRING(districtName, PATINDEX('%[0-9]%', districtName), PATINDEX('%[0-9][^0-9]%', districtName + 't') - PATINDEX('%[0-9]%', 
                    districtName) + 1) as integer), DistrictName

districtName
FORT BEND COUNTY MUD
FORT BEND COUNTY MUD 2
FORT BEND COUNTY MUD 23
FORT BEND COUNTY MUD 23 JT FAC
FORT BEND COUNTY MUD 122 123 JT FAC
FORT BEND COUNTY MUD 138
FORT BEND COUNTY MUD 169 - INTERNAL
FORT BEND COUNTY MUD 169 MASTER
FORT BEND COUNTY MUD 188 JT Sewer Plant
FORT BEND COUNTY MUD 188 JT Water Plant
FORT BEND COUNTY MUD 214 INTERNAL
FORT BEND COUNTY MUD 214 MASTER DISTRICT
FORT BEND COUNTY MUD 250A
FORT BEND COUNTY MUD 256