The following is being ran on 12.1.0.2.0.
I'm trying to do some very basic parsing of user-ids from free-form text fields when the data has been entered in certain patterns. I used the regular expression tester at https://regex101.com/#javascript to develop the regex and verify it against the sample data. On that site, the expression works and shows group 1 as being the desired subset of data. When running the extract and data through Oracle, null is returned instead. I don't see what I'm doing wrong so hoping someone can point it out for me.
SQL> WITH temp_table AS
2 (SELECT 'KEVIN MINION: /XBOX' idnt FROM dual
3 UNION ALL SELECT 'BOB MINION - XBOX LIVE' idnt FROM dual)
4 SELECT regexp_substr(idnt, '^([\w\s]*?)([:\s]+[\/-]\s?XBOX.*)$', 1, 1, 'c', 1) rslt
5 FROM temp_table;
RSLT
--------------------------------------------------------------------------------
SQL>
Here's the SQL without line numbers for ease of copying
WITH temp_table AS
(SELECT 'KEVIN MINION: /XBOX' idnt FROM dual
UNION ALL SELECT 'BOB MINION - XBOX LIVE' idnt FROM dual)
SELECT regexp_substr(idnt, '^([\w\s]*?)([:\s]+[\/-]\s?XBOX.*)$', 1, 1, 'c', 1) rslt
FROM temp_table;
The expected results are two rows:
KEVIN MINION
BOB MINION