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

CodePudding user response：

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