“Order By” Clause Using Substr Function

I like to show a example of Order By using a substr function. Usually people order results by columns but in general “Order by” clause (No only Oracle, in this case I refer standard, ANSI SQL/ISO 9075) is a powerfull option for sort in many differents ways.

In this example I order a string (which is a date) by day, month and year;

 
-- Date into the string have the next format;
-- Sun Oct 01 19:26:01 2017

select date_in_string from dual
order by 
  substr (date_in_string, 9, 2),
  substr (date_in_string, 5, 3),
  substr (date_in_string, 21, 4)
/

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s