CODEWITHSUNDEEP
sql-server-2008
jai
jai

Reputation: 21

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…..

Upvotes: 1

Views: 5621

Answers (1)

Pandian
Pandian

Reputation: 9126

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

Upvotes: 7

Related Questions