Q
Problem solve Get help with specific problems with your technologies, process and projects.

# Sorting by weekday as text

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;
```

#### Have a question for an expert?

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

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

#### Start the conversation

Send me notifications when other members comment.

## SearchDataManagement

• ### Databricks contributes Delta Lake to the Linux Foundation

Databricks has found a new home at the Linux Foundation for its open source Delta Lake data lake project, in a bid to help grow a...

• ### Apache Rya matures open source triple store database

Open source triple store database technology used by the U.S. Navy moves forward as a stable, mature project at the Apache ...

• ### PostgreSQL 12 boosts open source database performance

Widely used open source PostgreSQL database platform gets a major update providing users with new SQL query capabilities for JSON...

• ### SAS analytics platform aided by \$1 billion AI investment

The SAS suite of business intelligence products is quickly seeing results from the vendor's \$1 billion commitment to AI seven ...

• ### ThoughtSpot BI platform an early adopter of AI

Due to AI, ThoughtSpot's analytics tools have been accessible to citizen data scientists from the start, Andrew Yeung, the ...

• ### ThoughtSpot 6 advances AI power of BI platform

New augmented intelligence and machine learning tools, along with a new mobile app, highlight the release of the new update to ...

## SearchSAP

• ### How to improve the manufacturing process: 6 keys to tech success

AI, analytics, IoT and modern apps can serve important roles in today's manufacturing companies. Learn six steps that put these ...

New SAP co-CEOs Jennifer Morgan and Christian Klein must clarify SAP's products strategy, explain the experience economy and help...

• ### CEO of SAP Bill McDermott abruptly resigns

Bill McDermott, CEO of longtime ERP, CRM and BI software vendor SAP, resigned Thursday triggering a succession plan that promotes...

## SearchSQLServer

• ### SQL Server database design best practices and tips for DBAs

Good database design is a must to meet processing needs in SQL Server systems. In a webinar, consultant Koen Verbeeck offered ...

• ### SQL Server in Azure database choices and what they offer users

SQL Server databases can be moved to the Azure cloud in several different ways. Here's what you'll get from each of the options ...

• ### Using a LEFT OUTER JOIN vs. RIGHT OUTER JOIN in SQL

In this book excerpt, you'll learn LEFT OUTER JOIN vs. RIGHT OUTER JOIN techniques and find various examples for creating SQL ...

## TheServerSide.com

• ### Is Java slow? Compared to C++, it's faster than you think

If you find that Java is slower than other languages, such as C++, here's how to better compare the two and the major differences...

• ### Java video games, like Minecraft, keep the language popular

The next generation of Java developers can learn the ins and outs of the language through an unlikely source: video games such as...

• ### Pivotal, Microsoft team up to deliver Azure Spring Cloud

Azure Spring Cloud, jointly developed by Microsoft and Pivotal, lets Spring developers bring apps to the cloud without concern ...

## SearchDataCenter

• ### Improve server rack physical security with ISO standards

Safeguarding information access is essential to data center operations. With ISO Standard 27001 and the right locking mechanisms,...

• ### Essential private cloud migration steps

Executive buy-in, application evaluation and tool configuration are all important parts of cloud selection. With these steps, ...

• ### IBM quantum computers' usefulness in sight -- using binoculars

IBM's Bob Sutor discusses Big Blue's new quantum systems and computation center, the realities of quantum computing today and how...

## SearchContentManagement

• ### WebPurify launches profanity filter for memes and images

WebPurify has launched a beta of its Optical Character Recognition Profanity Filter Service to identify and block profane images ...

• ### Digital asset management benefits the future of work

Digital asset management promises to transform digital work, making it easier to tag documents, analyze video and provide image ...

• ### Box cloud content management adds PDF tools, boosts security

Box partners with Adobe in releasing a new set of PDF tools that can be used within Box's content management cloud to enable ...

## SearchHRSoftware

• ### A backlash emerges over automated interviewing

Illinois may have adopted the nation's first automated hiring law, which will take effect Jan 1. The law is likely to become a ...

• ### Get smarter about HR data security

HR deals with mountains of sensitive employee data and must do a better job of protecting that information. Here's a look at how ...

• ### Walmart, FedEx make case for VR training

The payback from virtual training may be improved retention and shorter training times. An hour-long training period can be ...

Close