Q

Sorting by weekday as text

This Content Component encountered an error

I have a table with one field which is VARCHAR2 datatype containing weekdays randomly like this:

FRIDAY
MONDAY
SATURDAY
SUNDAY
THURSDAY
TUESDAY
WEDNESDAY

I want to fetch data in order in a single SQL statement without using any user-defined functions. Is there any function in Oracle that returns the weekday (as a number) for a particular day (a string)? That is, if I pass 'WEDNESDAY', then it returns 4, ...
Solution 1: The simplest and fastest way to do it is with CASE or DECODE:

SELECT DISTINCT
        dow_val,
        weekday
FROM    (
            SELECT  weekday,
                    DECODE ( UPPER (TRIM (weekday)),
                             'SUNDAY',    1,
                             'MONDAY',    2,
                             'TUESDAY',   3,
                             'WEDNESDAY', 4,
                             'THURSDAY',  5,
                             'FRIDAY',    6,
                             'SATURDAY',  7,
                                          NULL  -- None of the above
                           )  AS dow_val
            FROM    table_x
        )
ORDER BY
        dow_val;

   DOW_VAL WEEKDAY
---------- ---------
         1 SUNDAY
         2 MONDAY
         3 TUESDAY
         4 WEDNESDAY
         5 THURSDAY
         6 FRIDAY
         7 SATURDAY

7 rows selected.

Solution 2: We can reduce the typing (and the efficiency) by replacing the big DECODE statement with:

        TO_CHAR ( NEXT_DAY ( SYSDATE, weekday),
                  'D'
                )

TO_CHAR (dt, 'D') returns a single character, not a number, but it works just as well for sorting. The first argument to TO_CHAR must be a DATE, and all we have is a string; that's why we call NEXT_DAY (dt, wstring), which returns a date whose day-of-the-week is given in wstring.

Solution 3: Look how much cleaner your code would be if you were willing to use a user-defined function:

SELECT DISTINCT
        dow_val,
        weekday
FROM    (
            SELECT  weekday,
                    dow (weekday)  AS dow_val
            FROM    table_x
        )
ORDER BY
        dow_val;

This solution is nearly as efficient as Solution 1, and it's cleaner than Solution 2. The function isn't hard to write, and you only have to write it once. With either of the solutions above, you have to repeat the code in every statement where you need it.

CREATE OR REPLACE FUNCTION  dow
(
    in_weekday_text  IN  VARCHAR2
)
RETURN  PLS_INTEGER

--      *************
--      **  d o w  **
--      *************

--  dow returns a number (1-7) corresponding to the day of
--  the week passed in in_weekday_text.  Inputs do not
--  need to be capitalized, and may include leading or trailing
--  spaces, but they do have to be full words (not like 'MON.')
--  If in_weekday_text is not one of the seven weekday names,
--  dow returns NULL.

IS
BEGIN
    RETURN  CASE  UPPER (TRIM (in_weekday_text))
                WHEN   'SUNDAY'    THEN  1
                WHEN   'MONDAY'    THEN  2
                WHEN   'TUESDAY'   THEN  3
                WHEN   'WEDNESDAY' THEN  4
                WHEN   'THURSDAY'  THEN  5
                WHEN   'FRIDAY'    THEN  6
                WHEN   'SATURDAY'  THEN  7
                                   ELSE  NULL
            END;
END;
/
SHOW ERRORS

GRANT  EXECUTE  ON  dow  TO  PUBLIC;

CREATE PUBLIC SYNONYM  dow  FOR  foo.dow;

This was first published in November 2003

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close