Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

regexp word boundary

user11290011Dec 11 2012 — edited Dec 11 2012
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
This post has been answered by Frank Kulash on Dec 11 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 8 2013
Added on Dec 11 2012
4 comments
1,977 views