Just to pile on a bit, use the desired collation to get the desired sort order

😎

Collation example with Lowell's data sample:

use tempdb;

go

;WITH MyCTE([ColumnName])

AS

(

SELECT N'000000000000001' UNION ALL

SELECT N'000000000000005' UNION ALL

SELECT N'000000000000006' UNION ALL

SELECT N'00000000000002N' UNION ALL

SELECT N'00000000000002S' UNION ALL

SELECT N'0002' UNION ALL

SELECT N'12-I395' UNION ALL

SELECT N'14-I395' UNION ALL

SELECT N'9-I395' UNION ALL

SELECT N'AL001' UNION ALL

SELECT N'AL002' UNION ALL

SELECT N'MD0001' UNION ALL

SELECT N'MD0002' UNION ALL

SELECT N'MD0003' UNION ALL

SELECT N'VA0001' UNION ALL

SELECT N'VA0002'

)

SELECT [ColumnName] COLLATE Arabic_100_BIN FROM MyCTE ORDER BY [ColumnName];