0

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

Tobias G.
  • 7
  • 3

1 Answers1

0

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

MT0
  • 143,790
  • 11
  • 59
  • 117
  • thank u for your answer with both expressions i get the result null – Tobias G. Nov 23 '21 at 09:31
  • @TobiasG. The db<>fiddle linked in the answer does not give `NULL` for any of your examples. If you are getting different results then please give an example with the data as `CREATE TABLE` and `INSERT` statements we can copy-paste because I cannot replicate your `NULL` outputs. – MT0 Nov 23 '21 at 09:36
  • if i replicate it with your code and create the new table, it is correct, if i use the table from my database with its value the outcome is null. i tried to copy the value and paste it to notepad++ i got this result: "39 Euro [[WIN]] \n49 Euro \n59 Euro" with breaking lines – Tobias G. Nov 23 '21 at 09:48
  • i meant with hyphenates " and breaking – Tobias G. Nov 23 '21 at 09:56
  • @TobiasG. That is because your data does not match the question you asked and has white-space after the `[[WIN]]` substring. I've updated the answer but if you don't give us the correct input then the answer you get will be for the incorrect values and may not work. – MT0 Nov 23 '21 at 09:56
  • thank you - now i get the correct results out of the database – Tobias G. Nov 23 '21 at 10:00