I need to order a result set by a substring of the file name. The substring is the date.

For example:

select * from mytable where file_name like ' 2112%'
order by name_file desc;

Result set is for example this:

1. qwerty_20211220_120220
2. asdfgh_20211229_121005
3. zxcvbn_20211223_121151

How to do for order by a portion of string date contains in name_file?

thanks to everyone

CodePudding user response：

You need to write an expression that extracts the date from the string and then use that expression in your ORDER BY.

An easy way to do that, with your sample data, might be:

select * from files
order by substr(file_name,-15);

I.e., sort by the last 15 positions of the filename (which are the date).

If you are on 12.2 or later, you can do a little better with this:

select * from files
order by to_date(substr(file_name,-15) 
             default null on conversion error,'YYYYMMDD"_"HH24MISS') nulls last;

This will make sure the data you are sorting on are actual dates. Any file name in your last that does not have a date stamp will be sorted last.

CodePudding user response：

Alternatively, sort by "date" (which is represented by the 1st group of numbers):

SQL> select *
  2  from mytable
  3  order by regexp_substr(file_name, '\d ');

FILE_NAME
----------------------
qwerty_20211220_120220
zxcvbn_20211223_121151
asdfgh_20211229_121005

Or, sort by anything that follows the 1st underline character:

SQL> select *
  2  from mytable
  3  order by substr(file_name, instr(file_name, '_'));

FILE_NAME
----------------------
qwerty_20211220_120220
zxcvbn_20211223_121151
asdfgh_20211229_121005

SQL>