Oracle REGEXP_SUBSTR match words delimited by newline "\n"

Question

I'm trying to split the string "SMITH\nALLEN\WARD\nJONES" in Oracle 10g by newline "\n" using a single SELECT query. Please help me to find the exact Regular Expression for this.

Expected Output:

1 SMITH
2 ALLEN\WARD
3 JONES

I have tried the expression "[^\n]+", but I got the following result which considered "\" as well. I want the expression to consider only "\n".

Query:

    SELECT   REGEXP_SUBSTR ('SMITH\nALLEN\WARD\nJONES',
                            '[^\n]+',
                            1,
                            LEVEL)
      FROM   DUAL
CONNECT BY   REGEXP_SUBSTR ('SMITH\nALLEN\WARD\nJONES',
                            '[^\n]+',
                            1,
                            LEVEL) IS NOT NULL;

Result:

1 SMITH
2 ALLEN
3 WARD 
4 JONES

Answer 1

Within the [] the characters are matched individually, and \n isn't seen a special character or even a run of characters anyway - it matches either of those characters individually; so even changing to [^\\n] wouldn't help.

You can use this form, which also returns empty elements:

select regexp_substr('SMITH\nALLEN\WARD\nJONES', '(.*?)(\\n|$)', 1, level, 'm', 1)
from dual
connect by level <= regexp_count('SMITH\nALLEN\WARD\nJONES', '\\n') + 1;

REGEXP_SUBSTR('SMITH\NAL
------------------------
SMITH
ALLEN\WARD
JONES