Breaking News: Grepper is joining You.com. Read the official announcement!

regexp_substr ORACLE

Wide-eyed Wolf answered on March 30, 2022 Popularity 6/10 Helpfulness 3/10

answer regexp_substr ORACLE

related regexp_substr in oracle

regexp_substr ORACLE

Comment

-- library_books : id, title, author, genders, total_views ...
/*
    Library Books
    (1, 'The Great Gatsby 2013', 'F. Scott Fitzgerald', 'Fiction; Drama; Romance', 84)
    (2, '2000 - The year of the demon', 'Sebastian Whatever', 'Fiction; Drama; Mystery', 59)
    (3, '1984', 'George Orwell', 'Fiction; Drama; Science Fiction', 12)
    (4, 'Pride and Prejudice', 'Jane Austen', 'Fiction; Drama; Romance', 6)
    (5, '2103: The Deadly Wake', 'G. Philip Jackson', 'Fiction; Drama; Mystery', 101)
    (6, 'Animal Farm', 'George Orwell', 'Fiction; Drama; Science Fiction', 99)
    (7, 'Cyborg 2087', 'Franklin Adreon', 'Fiction; Thriller', 24)
    (8, '2069: A Sex Odyssey', 'Georg Tressler', 'Fiction; Comedy', 75)
    (9, '3022', 'John Suits', 'Fiction; Mystery', 3)
    (10, 'The Alchemist', 'Paulo Coelho', 'Fiction; Drama; Fantasy', 278)
*/
WITH bk_gender AS
(
    SELECT 
        regexp_substr(genders, '[^;]+', 1, level) AS gender,
        total_views
    FROM 
        library_books
    CONNECT BY 
        regexp_substr(genders, '[^;]+', 1, level) IS NOT NULL
        AND PRIOR genders = genders
        AND PRIOR sys_guid() IS NOT NULL
)
SELECT 
    gender,
    sum(total_views) AS total_views
FROM 
    cats
GROUP BY 
    gender
ORDER BY 
    total_views DESC
    
-- Another example - Year of the book (Year is on the title)
SELECT
    title,
    TO_NUMBER(REGEXP_SUBSTR(title, '\d{4}')) AS vintage_year
FROM
    library_books
WHERE
    1 = 1
    AND REGEXP_LIKE(title, '\d{4}')

Popularity 6/10 Helpfulness 3/10 Language sql

Source: Grepper

Tags: oracle sql

Link to this answer
Share Copy Link

Contributed on Jun 10 2024

Wide-eyed Wolf

0 Answers Avg Quality 2/10

Closely Related Answers

regexp_substr in oracle

Comment

REGEXP_SUBSTR( string, pattern [, start_position [, nth_appearance [, match_parameter [, sub_expression ] ] ] ] )

Popularity 9/10 Helpfulness 2/10 Language whatever

Source: www.techonthenet.com

Tags: oracle whatever wh

Link to this answer
Share Copy Link

Contributed on Mar 30 2022

Parvez

0 Answers Avg Quality 2/10

regexp_substr ORACLE

Contents

More Related Answers

regexp_substr ORACLE

Closely Related Answers

regexp_substr in oracle

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.