SQL how to sort text with numbers alphabetically, then numerically, with additional string to right

Question

Data stored in a varchar column looks like this:

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

I'm trying to sort name first then if name contains number sort by number.

I am currently using this SQL code:

SELECT DistrictName
FROM Master
ORDER BY LEFT(DistrictName, PATINDEX('%[0-9]%', DistrictName + '0') - 1), LEN(DistrictName), DistrictName

It works well unless there is an additional string on the right side of the number. I would like the "23 JT FAC" to sort under "MUD 23" etc. So sorting name first, and if name contains a number sort the numbers. How could I modify this query to make this happen?

Thanks,

Answer 1

You could extract the number portion and cast as integer.

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

fiddle