Extract a number from an URL

In this example:

If you want to use the extracted number as a numeric value, you can convert it using the TO_NUMBER function

CREATE OR REPLACE FUNCTION extract_number_from_url(p_url IN VARCHAR2) RETURN NUMBER IS
    v_number NUMBER;
BEGIN
    v_number := TO_NUMBER(REGEXP_SUBSTR(p_url, '\d+$'));
    RETURN v_number;
END;
/

-- Example usage:
DECLARE
    url VARCHAR2(100) := 'https://www.domain.com/21';
    number_extracted NUMBER;
BEGIN
    number_extracted := extract_number_from_url(url);
    DBMS_OUTPUT.PUT_LINE(number_extracted);  -- prints: 21
END;
/

In this example:

REGEXP_SUBSTR is a function that extracts a substring from a string using a regular expression pattern.

The pattern \d+$ matches one or more digits (\d+) at the end of the string ($).

The result is stored in the number_extracted variable and then printed using DBMS_OUTPUT.PUT_LINE

DECLARE
    url VARCHAR2(100) := 'https://www.domain.com/21';
    number_extracted VARCHAR2(20);
BEGIN
    number_extracted := REGEXP_SUBSTR(url, '\d+$');
    DBMS_OUTPUT.PUT_LINE(number_extracted);  -- prints: 21
END;
/

Example

DECLARE
    v_clob CLOB;
    v_pattern VARCHAR2(4000) := '<img src="https://www.domain.com/file/([0-9]+)';
    v_match VARCHAR2(4000);
    v_number NUMBER;
    v_offset NUMBER := 1;
    v_occurrence NUMBER := 1;
    v_return_pos NUMBER;
    v_id NUMBER;
BEGIN
    FOR cur_rec IN (SELECT id, clob_column FROM sample_clob_table) LOOP
        v_clob := cur_rec.clob_column;
        v_id := cur_rec.id;
        v_offset := 1;
        LOOP
            v_match := REGEXP_SUBSTR(v_clob, v_pattern, v_offset, v_occurrence, 'i', 1);
            EXIT WHEN v_match IS NULL;
            v_number := TO_NUMBER(v_match);
            DBMS_OUTPUT.PUT_LINE('ID: ' || v_id || ', Extracted Number: ' || v_number);
            v_offset := v_offset + LENGTH(v_match) + LENGTH('<img src="https://www.oracle.com/file/');
        END LOOP;
    END LOOP;
END;
/

Example

--
CREATE OR REPLACE TYPE number_assoc_array IS TABLE OF NUMBER;

-- Create a sample table
CREATE TABLE sample_clob_table (
    id NUMBER,
    clob_column CLOB
);

-- Insert sample data
INSERT INTO sample_clob_table (id, clob_column) VALUES (
    1,
    '<img src="https://www.oracle.com/file/90123"><img src="https://www.oracle.com/file/94567"> some other text'
);

INSERT INTO sample_clob_table (id, clob_column) VALUES (
    2,
    '<img src="https://www.oracle.com/file/90123"><img src="https://www.oracle.com/file/22222"> some other text'
);

COMMIT;

-- PL/SQL procedure to process CLOB and collect unique numbers
DECLARE
    TYPE number_assoc_array IS TABLE OF BOOLEAN INDEX BY NUMBER;
    v_pattern VARCHAR2(4000) := '<img src="https://www.oracle.com/file/([0-9]+)';
    v_match VARCHAR2(4000);
    v_number NUMBER;
    v_offset NUMBER := 1;
    v_occurrence NUMBER := 1;
    v_numbers number_assoc_array;
BEGIN
    FOR cur_rec IN (SELECT clob_column FROM sample_clob_table) LOOP
        v_offset := 1;
        LOOP
            v_match := REGEXP_SUBSTR(cur_rec.clob_column, v_pattern, v_offset, v_occurrence, 'i', 1);
            EXIT WHEN v_match IS NULL;
            v_number := TO_NUMBER(v_match);
            IF NOT v_numbers.EXISTS(v_number) THEN
                v_numbers(v_number) := TRUE;
                DBMS_OUTPUT.PUT_LINE('Extracted Number: ' || v_number);
            END IF;
            v_offset := v_offset + LENGTH(v_match) + LENGTH('<img src="https://www.oracle.com/file/');
        END LOOP;
    END LOOP;
    
    -- Print the collected unique numbers
    DBMS_OUTPUT.PUT_LINE('Unique Numbers:');
    v_number := v_numbers.FIRST;
    WHILE v_number IS NOT NULL LOOP
        DBMS_OUTPUT.PUT_LINE(v_number);
        v_number := v_numbers.NEXT(v_number);
    END LOOP;
END;
/

Example

CREATE OR REPLACE TYPE number_assoc_array IS TABLE OF NUMBER;
CREATE OR REPLACE PACKAGE my_types IS
    TYPE number_assoc_array IS TABLE OF BOOLEAN INDEX BY NUMBER;
END;
/
CREATE OR REPLACE PACKAGE my_types IS
    TYPE number_assoc_array IS TABLE OF BOOLEAN INDEX BY NUMBER;
END;
/

-- Create a sample table
CREATE TABLE sample_clob_table (
    id NUMBER,
    clob_column CLOB
);

-- Insert sample data
INSERT INTO sample_clob_table (id, clob_column) VALUES (
    1,
    '<img src="https://www.oracle.com/file/90123">
    <img src="https://www.oracle.com/file/94567">
    some other text'
);

INSERT INTO sample_clob_table (id, clob_column) VALUES (
    2,
    '<img src="https://www.oracle.com/file/90123">
    <img src="https://www.oracle.com/file/22222">
    some other text'
);

COMMIT;

-- PL/SQL procedure to process CLOB and extract unique numbers
DECLARE
    TYPE number_assoc_array IS TABLE OF BOOLEAN INDEX BY NUMBER;
    v_pattern VARCHAR2(4000) := '<img src="https://www.oracle.com/file/([0-9]+)';
    v_match VARCHAR2(4000);
    v_number NUMBER;
    v_offset NUMBER := 1;
    v_occurrence NUMBER := 1;
    v_numbers number_assoc_array;
    v_line VARCHAR2(4000);
    v_pos NUMBER;
BEGIN
    FOR cur_rec IN (SELECT clob_column FROM sample_clob_table) LOOP
        v_offset := 1;
        LOOP
            v_pos := DBMS_LOB.INSTR(cur_rec.clob_column, CHR(10), v_offset);
            IF v_pos = 0 THEN
                v_line := DBMS_LOB.SUBSTR(cur_rec.clob_column, DBMS_LOB.GETLENGTH(cur_rec.clob_column) - v_offset + 1, v_offset);
                v_offset := DBMS_LOB.GETLENGTH(cur_rec.clob_column) + 1;
            ELSE
                v_line := DBMS_LOB.SUBSTR(cur_rec.clob_column, v_pos - v_offset, v_offset);
                v_offset := v_pos + 1;
            END IF;
            v_match := REGEXP_SUBSTR(v_line, v_pattern, 1, v_occurrence, 'i', 1);
            IF v_match IS NOT NULL THEN
                v_number := TO_NUMBER(v_match);
                IF NOT v_numbers.EXISTS(v_number) THEN
                    v_numbers(v_number) := TRUE;
                    DBMS_OUTPUT.PUT_LINE('Extracted Number: ' || v_number);
                END IF;
            END IF;
            EXIT WHEN v_offset > DBMS_LOB.GETLENGTH(cur_rec.clob_column);
        END LOOP;
    END LOOP;
    
    -- Print the collected unique numbers
    DBMS_OUTPUT.PUT_LINE('Unique Numbers:');
    v_number := v_numbers.FIRST;
    WHILE v_number IS NOT NULL LOOP
        DBMS_OUTPUT.PUT_LINE(v_number);
        v_number := v_numbers.NEXT(v_number);
    END LOOP;
END;
/