Hi,
I am working on oracle 11g. I need help to get the whole string before the word Contact1: . Please see some examples below.
SELECT REGEXP_SUBSTR('Day:
Work:
Contact1:
Contact2:
Contact3:','^[^\WContact1:\W]+')
FROM dual;
When i look at the number on new lines in the above string i am getting the value as 5. And when i am trying to pull the 3rd word before the new line I am geting it as Contact1: instead of null.
SELECT REGEXP_COUNT('Day:
Work:
Contact1:
Contact2:
Contact3:',CHR(10))
FROM dual;
output: 5
SELECT REGEXP_COUNT('Day:
Work:
xyzadbc
Contact1:
Contact2:
Contact3:',CHR(10))
FROM dual;
output: 5
SELECT REGEXP_SUBSTR('Day:
Work:
xyzadbc
Contact1:
Contact2:
Contact3:','[^'||CHR(10)||']+',1,3)
FROM dual;
output: xyzadbc
SELECT REGEXP_SUBSTR('Day:
Work:
Contact1:
Contact2:
Contact3:','[^'||CHR(10)||']+',1,3)
FROM dual;
output: Contact1: (how can i get a null for this)
Appreciate your help.
Jacky