By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH CTE AS
(
SELECT 'employee1' Name
union all select 'employee2'
union all select 'employee3'
union all select 'employee6'
union all select 'employee6'
union all select 'employee10'
union all select 'employee11'
union all select 'employee12'
union all select 'employee17'
union all select 'employee82'
union all select 'employee100'
union all select 'employee111'
union all select 'employee129'
union all select 'employee299'
)
SELECT Name
FROM CTE
ORDER BY CAST(SUBSTRING(Name, PATINDEX('%[^a-z, '' '']%', Name), LEN(Name)) AS INT)
| Name |
|---|
| employee1 |
| employee2 |
| employee3 |
| employee6 |
| employee6 |
| employee10 |
| employee11 |
| employee12 |
| employee17 |
| employee82 |
| employee100 |
| employee111 |
| employee129 |
| employee299 |