Trending September 2023 # How Extract() Function Works In Oracle? # Suggested October 2023 # Top 18 Popular

You are reading the article How Extract() Function Works In Oracle? updated in September 2023 on the website Happystarlongbien.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 How Extract() Function Works In Oracle?

What is an Oracle EXTRACT() Function?

An Oracle EXTRACTFunction is used to extract components from the given input, as EXTRACT function name suggests. It extracts and returns a specified date-time value or interval value.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

It returns a string containing the appropriate time zone name or abbreviation if we extract it.

This is a very useful function for manipulating date-time in a large table.

Oracle 9i or later versions supports EXTRACT() function.

It is used to extract TIMESTAMP, DATE. Interval value or XML components from the given input.

In ambiguity case for the combinations of date-time field and interval value expression, Oracle returns UNKNOWN as a result.

Syntax

EXTRACT(Component FROM [Date_time or Interval_valueexpression])

Description

COMPONENT: It is listed above, it is to be extracted.

[Date_time or Interval_valueexpression]: It can be a DATE or TIMESTAMP or an INTERVAL value from which a field can be extracted using components.

Syntax to Extract XML Components

EXTRACT(xml-type-instance, 'xpath', 'namespace')

Description

This function used to select a node or set of nodes with its leaf node. This is similar to the EXISTNODEfunction

This function accepts two arguments.

xml-type-instance: It is an object value

‘xpath’: This is the path from where function extracts nodes details. It can be an absolute or relative type.

namespace: It is an optional parameter.

How EXTRACT() Function Works in Oracle?

As the extract function name suggests it’s a function which extracts some of the detail from the given expression or value. It accepts component and based on it, extract value from the Date_time or Interval_valueexpression.

If component is YEAR / MONTH then expression datatype must be DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL YEAR TO MONTH.

If a component is DAY then expression data type must be DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL DAY TO SECOND.

If component is HOUR or MINUTE or SECOND then-expression datatype must be TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL DAY TO SECOND. In Oracle DATE data type does not store time or time field.

If component is TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_ABBR, TIMEZONE_REGION or TIMEZONE_OFFSETthenexpression datatype must be TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE.

Implementations of EXTRACT() Function with Examples

In this section, we’ll see the implementation of Oracle EXTRACT Function and its behavior. For that, we will use the below sample table (Employee&Msg) with 14 & 4records to understand the Oracle EXTRACT() function behavior.

SELECT * FROM Employee;

Output:

SELECT Dtime FROM Msg;

Output:

EXTRACT() Function

SELECT DOJ, EXTRACT(Month FROM DOJ) MONTH FROM Employee;

Output:

EXTRACT()Function with DATE Data Type

SELECT DOJ, EXTRACT(YEAR FROM DOJ) YEAR FROM Employee;

Output:

In the above example, EXTRACT function takes Year component and returns the numeric value of the Year in YYYY format from the given expression i.e. DOJ column from Employee table.

SELECT DOJ, EXTRACT(Hour FROM DOJ) HOUR FROM Employee;

Output:

The above SQL statement throws an error, WHY?

Because in the above EXTRACT function component is Hour but the expression or column is a DATE data type which does not store time information or data. So Extract function tries to extract the Hour but it couldn’t find Hour from the column data. That’s why it throws an error.

EXTRACT() Function with TIMESTAMP Data Type

In the Msg table, Dtime column is a TIMESTAMP data type which contains time details with date and TIME_ZONE.

Output:

In the above example, the EXTRACT function takes the Hour component and extractsHour information in 24 Hour format from the given expression i.e. DOJ column from the Employee table.

EXTRACT() Function

It is very useful for splitting TIME or TIMESTAMP data into multiple columns as per requirement.

FROM Msg;

Output:

The above EXTRACT function example shows that it’s very powerful function for extracting information from DATE, TIMESTAMP or Interval_valueexpression.

Tips:

While using EXTRACT function, take extra care about DATE and TIMESTAMP data type expression.

Conclusion

Oracle EXTRACT() function is very useful function for extracting information from DATE, TIMESTAMP or Interval_valueexpression.This function is used to bifurcate the DATE OR TIMESTAMP data type using component like month. Date etc.