Search code examples
oracleregexp-substr

I want to extract a string with escaping sign using regex in ORACLE SQL

I have got a string with multiple possible ANSWERS but only one is correct:

  • "shop.com\nshop.net\nvouchers.com [[WIN]]"
  • "39 Euro [[WIN]]\n49 Euro\n59"
  • "Euro 7 things\n12 things[[WIN]]\n21 things"

I need to extract the right/winning answer:

  • "vouchers.com"
  • "39 Euro"
  • "12 things"

Thanks for your help

Solution

  • If your data contains newline CHR(10) characters then you can use:

    SELECT REGEXP_SUBSTR(value, '^(.*)\[\[WIN\]\]$', 1, 1, 'm', 1) AS match
FROM   table_name

    Which, for the sample data:

    CREATE TABLE table_name (value) AS
SELECT 'shop.com
shop.net
vouchers.com [[WIN]]' FROM DUAL UNION ALL
SELECT '39 Euro [[WIN]]
49 Euro
59' FROM DUAL UNION ALL
SELECT 'Euro 7 things
12 things[[WIN]]
21 things' FROM DUAL;

    Outputs:

    MATCH
    vouchers.com
    39 Euro
    12 things

    If your data contains \n two-character substrings then:

    SELECT REGEXP_SUBSTR(value, '^(.*\\n)?(.*?)\[\[WIN\]\]\s*($|\\n)', 1, 1, NULL, 2)
         AS match
FROM   table_name

    db<>fiddle here