Sql order by string and int-CodePudding

I have table with column days, this days value is like this:

day 1
day 2
day 3
day 4
...
day n

In my database these row are not ordered, so i want to retrieve it ordered by the int after the string: example i have

day 2
day 4
day 1
day 3

=> to got

day 1
day 2
day 3
day 4

Changing column type and value is the best option but i want to work with this structure if there is an option to retrieve it.

CodePudding user response：

The orderby clause can use the col and a right function in order.

select * from tablename order by right(day,2) asc

Hopping the days got from 1 to max 99 ... if more maybe looking at the space between the day and the number could be another way.

CodePudding user response：

should work:

select * from orderby  order by day asc

Edit:

Sorry I didn't notice that its string int:

Fixed my code:

select * from orderby order by convert(int, replace(sdate, 'day ', '')) asc

CodePudding user response：

select a, CAST(substring(a from '\d $') as int) Num from test_tbale order by Num;

result

 Day 0 | 0
 Day 1 | 1
 Day 3 | 3
 Day 4 | 4
 Day 5 | 5
 Day 7 | 7

current data

select * from test_table;
   a
-------
 Day 3
 Day 4
 Day 5
 Day 7
 Day 0
 Day 1

Postgres

same result using split_part

select a, split_part(a , ' ', 2)::int Num from test_table order by Num;

   a   | num
------- -----
 Day 0 | 0
 Day 1 | 1
 Day 3 | 3
 Day 4 | 4
 Day 5 | 5
 Day 7 | 7

complete example

create table test_table(a text);
insert into test_table select 'Day ' || floor(random()*100  1)::text from generate_series(1,10);

select * from test_table;
   a
--------
 Day 19
 Day 87
 Day 86
 Day 34
 Day 50
 Day 97
 Day 84
 Day 81
 Day 67
 Day 6


select a, CAST(substring(a from '\d $') as int) Num from test_table order by Num;
   a    | num
-------- -----
 Day 6  |   6
 Day 19 |  19
 Day 34 |  34
 Day 50 |  50
 Day 67 |  67
 Day 81 |  81
 Day 84 |  84
 Day 86 |  86
 Day 87 |  87
 Day 97 |  97