CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Aavadesh Patel
Aavadesh Patel

Reputation: 11

SELECT query ordering by substring in Sql Server

I have a column in a SQL Server table that has the following rows:

MyColumn : 

1 Month
2 Week
10 Minutes
1 week
12 hours
1 days
2 month
2 day
5 minutes
1 hours

It is a text column that contains Priority strings.

Is there a way to make a select return this column ordered as following:

10 Minutes
5 minutes
1 hours
10 Hours
1 day
2 days
1 week
2 weeks
1 month
2 months

etc..

thank you

Upvotes: 1

Views: 853

Answers (3)

Joe G Joseph
Joe G Joseph

Reputation: 24046

select T4.cnt +' '+T4.name from (         
select substring(name, 1, CHARINDEX(' ', name)) cnt,substring(name,CHARINDEX(' ', name)+1,LEN(name)) name from test4) T4      
left outer join (
select 1 as id,'Month' As name union all
select 2 as id,'Week' As name union all
select 3 as id,'Day' As name union all
select 4 as id,'Minutes' As name )T6
on t4.name=t6.name
order by t6.id,t4.cnt

You have to give all the distinct values (month,week etc..) in the order you want in the left table with "union all"

Upvotes: 0

Zane Bien
Zane Bien

Reputation: 23125

Try this solution:

SELECT mycolumn
FROM tbl
ORDER BY
    SUBSTRING(mycolumn, PATINDEX('%[^0-9]%', mycolumn)+1, 999),
    CAST(LEFT(mycolumn, PATINDEX('%[^0-9]%', mycolumn)-1) AS INT)

SQL-Fiddle Demo

Upvotes: 1

juergen d
juergen d

Reputation: 204756

order by case when patindex('%Month', MyColumn) > 0
              then 0
              when patindex('%week', MyColumn) > 0
              then 1
              when patindex('%days', MyColumn) > 0
              then 2
              when patindex('%Minutes', MyColumn) > 0
              then 3
         end, 
         cast(substring(MyColumn, 1, CHARINDEX(' ', MyColumn)) as int)

Upvotes: 0

Related Questions