Keywords: Oracle | function | regular expression | number extraction | REGEXP_REPLACE
Abstract: This article explains how to create a custom function in Oracle Database to extract all numbers from strings containing letters and numbers. By using the REGEXP_REPLACE function with patterns like [^0-9] or [^[:digit:]], non-digit characters can be efficiently removed. Detailed examples of function creation and SQL query applications are provided to assist in practical implementation.
Problem Context
In Oracle database development, there is often a need to extract numbers from mixed strings. For instance, given a string like "RO1234", the goal is to extract the numeric part "1234". This requirement is common in data processing and query optimization.
Core Solution
Oracle offers robust regular expression support through the REGEXP_REPLACE function. By using patterns like [^0-9] or [^[:digit:]], all non-digit characters can be removed by replacing them with an empty string.
Creating a Custom Function
To facilitate easier usage in SQL queries, a custom function extract_number can be created. This function takes a string parameter and returns the extracted numeric string.
CREATE FUNCTION extract_number(in_number VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN REGEXP_REPLACE(in_number, '[^[:digit:]]', '');
END;Application Example
After creating the function, it can be directly invoked in SQL queries. For example, to query records with a specific number:
SELECT DISTINCT column_name, extract_number(column_name)
FROM table_name
WHERE extract_number(column_name) = 1234;Additional Notes
While using REGEXP_REPLACE directly in queries might be more efficient, a custom function enhances code readability and reusability. Additionally, consider handling cases like empty strings or invalid inputs.