Sort varchar datatype with numeric characters

I have a varchar column that needs to be sorted by the number included in it. The data is similar to:

Group 1
Group 10
Group 11
Group 12
Group 13
Group 14
Group 15
Group 16
Group 17
Group 18
Group 19
Group 2
Group 20
Group 3
Group 4
Group 5
Group 6
Group 7
Group 8
Group 9
Test Group

I want the output like this where the value is sorted by the number.

Group 1
Group 2
Group 3
Group 4
Group 5…..

Answers


TRY like below, It will help you..

SELECT ColumnName FROM TableName 
ORDER BY CONVERT(INT, Replace(ColumnName, 'Group','')) 

SQL Fiddle : http://sqlfiddle.com/#!3/e14a6/7

Another Way

SELECT ColumnName FROM TableName 
ORDER BY LEN(ColumnName),ColumnName

SQL Fiddle : http://sqlfiddle.com/#!3/e14a6/6

Another Way

SELECT ColumnName FROM TableName order by 
case when 
    PATINDEX('%[^0-9]%',ColumnName) = 0 
THEN 
    data
ELSE
    cast(Left(ColumnName,PATINDEX('%[^0-9]%',ColumnName)-1) as int) 
END

SQL Fiddle : http://sqlfiddle.com/#!3/14eb5/2

Another Way

Here i have add another solution by using Common Table Expression Try this...

with tempCTE(Data, pos)
as
(select data, Patindex('%[0-9]%', data)  from sample),
tempCTE2(name, num)
as
(select SUBSTRING(data, 0, pos) name , cast(SUBSTRING(data, pos , LEN(data)) as int) num from tempCTE)
select name + CAST(num as varchar(10)) num1 from tempCTE2 order by name, num asc

SQL Fiddle : http://sqlfiddle.com/#!3/14eb5/3


Need Your Help

How to check a c programs's main function's return value from another c program

c child-process return-code

I am trying to test whether a c program has compiled and executed correctly or not.

how can i store output voice to an audio file in freetts

java voice-recognition

I am trying to use freetts for a simple java application but i am facing a problem, can anyone tell me how can i save the output voice which is converted from text to speech into a wave file in my

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.