SELECT query ordering by substring in Sql Server

Question

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

I think it would be a rather complex query condition. Probably, you should add another table with mapping of your strings to seconds, for example, and order based on the values in this table. — Andrew Logvinov
– Andrew Logvinov, Commented Jul 10, 2012 at 7:47
The desired ordering seems strange in that it is descending length of duration units but ascending within each group. Is that definitely what you want? — Martin Smith
– Martin Smith, Commented Jul 10, 2012 at 7:51
Could you have 6 weeks? And if so, should that sort above 1 month but below 2 months? — Sean
– Sean, Commented Jul 10, 2012 at 8:00
Consider storing values and units in separate columns. If you often need to produce strings made of these two things, add also a computed column to the same table: AmountDisplay AS CAST(Value AS varchar(10)) + ' ' + Unit + CASE Value WHEN 1 THEN '' ELSE 's' END or something like that. Thus, you would store 10 into Value, Minute` into Unit and read 10 Minutes from AmountDisplay. And it would then be easier to introduce custom sorting, like the kind you are asking about. — Andriy M
– Andriy M, Commented Jul 10, 2012 at 9:12

Zane Bien · Accepted Answer · 2012-07-10 08:10:20Z

1

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

answered Jul 10, 2012 at 8:10

Zane Bien

23.2k6 gold badges47 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

juergen d · Accepted Answer · 2012-07-10 07:57:47Z

0

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)

edited Jul 10, 2012 at 7:57

answered Jul 10, 2012 at 7:52

juergen d

205k40 gold badges304 silver badges377 bronze badges

1 Comment

Sean Over a year ago

You'll need to extract the numeric value from MyColumn else it will sort "1 Month" ahead of "11 Months"

Joe G Joseph · Accepted Answer · 2012-07-10 10:19:57Z

0

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"

edited Jul 10, 2012 at 10:19

answered Jul 10, 2012 at 8:34

Joe G Joseph

24.2k5 gold badges58 silver badges58 bronze badges

Collectives™ on Stack Overflow

SELECT query ordering by substring in Sql Server

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related