Sort data in varchar alphabet order

This is another way. Assuming your number strings are greater than -100000000. SELECT YourString FROM YourTable ORDER BY CASE WHEN YourString LIKE '[0-9]%' THEN CONVERT(int, YourString) ELSE -100000000 END, YourString

I tried to split your field in two, alpha and num. In order to order the query by Alpha, first and Num, second. But AAAAA1 is after AAAA01 with my logic... Do you have an explicite rule to explain why do you want AAAA01 before AAAAA1 ? SELECT SECTION.section, CASE WHEN section NOT LIKE '%[^A-Z]' THEN section WHEN section LIKE '[A-Z]%' THEN LEFT (section, PATINDEX ('%[^A-Z]%', section)-1) ELSE 'ZZZZZZZZZZZZZ' END AS Alpha, CASE WHEN ISNUMERIC(section)=1 THEN CAST( section AS int) WHEN SECTION.section LIKE '%[0-9]' THEN CAST( RIGHT (section, PATINDEX ('%[^0-9]%', REVERSE (section))-1 ) AS INT) ELSE 0 END AS Num FROM SECTION ORDER BY Alpha, Num; http://sqlfiddle.com/#!3/a1a03/1

How about this SQL? i tried with your data and it's returned the correct order SELECT section FROM section ORDER BY LEN(LEFT(Section, PATINDEX('%[0-9]%', Section)-1)) DESC Responding to @A.Goutam's comment below (i'm sorry i don't know how to add image to the comment) I just copied the data given by @A.Goutam and tried it directly in my sql server as shown below http://i.stack.imgur.com/o9aVB.png New SQL query with new data SELECT section AS section FROM section ORDER BY LEN(section + '1') DESC , LEN(LEFT(section + '1', PATINDEX('%[0-9]%', section + '1')-1)) DESC , CONVERT(INT,SUBSTRING(section + '1',PATINDEX('%[0-9]%',section + '1'),LEN(section + '1'))) DESC Result: AAAAAA1 AAAAA01 AAAAA01 AAAAA01 AAAAA01 AAAAA01 ABAAAA AB100 B32 B21 A10 B3 B1 11 2 1

You can accomplish what you're trying to get with temporary table as well. However, for larger records this can add some overhead to performance. So, i suggest you to use it sparingly. CREATE table #temp ( Section varchar(20) ) INSERT INTO #temp SELECT * FROM dbo.Section WHERE section LIKE '[A-Z]%' order by section ASC INSERT INTO #temp SELECT * FROM dbo.Section WHERE section LIKE '[0-9]%' order by section ASC SELECT * FROM #temp drop table #temp