Sorting in SQL query in ascending order

You can use below query to get desired result.

SELECT columnName  FROM TableName
ORDER BY SUBSTRING(columnName,0,3) ,SUBSTRING(columnName,7,4), SUBSTRING(columnName,4,7)

Note: If you have lots of rows in table, it might have some performance impact. In that case you can keep values in auto calculated columns and apply sorting on that.

As OriginalGriff pointed out, never store several values in a single column. Doing so will introduce a whole bunch of unnecessary complexity in your program, for example the question you're currently asking.

If you would have three columns, you could easily define the sorting as

ORDER BY Col1, Col2, Col3

And that's it. No complexity at all.

If, for some reason, you need the database to be able to show the three fields concatenated together, you can always so it in the select statement, use a view or create a computed column.

Here is the solution

select * from temp
   order by
   left(columnA,2) asc ,
   right(columnA,4) asc,
   cast(replace(replace(columnA,left(columnA,3),''),right(columnA,5),'') as int) asc