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.
EXTRACT(Component FROM [Date_time or Interval_valueexpression])
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')
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;
SELECT Dtime FROM Msg;
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;
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;
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.
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.
The above EXTRACT function example shows that it’s very powerful function for extracting information from DATE, TIMESTAMP or Interval_valueexpression.
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.Recommended Articles
This is a guide to Oracle EXTRACT(). Here we discuss a what is Oracle EXTRACT(), with appropriate syntax and respective examples for better understanding. You can also go through our other related articles to learn more –
You're reading How Extract() Function Works In Oracle?
Update the detailed information about How Extract() Function Works In Oracle? on the Happystarlongbien.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!