Extract initials from string - oracle syntax

Hello
Have a string field which may contain one or more names seperated by a comma. I need to return just the Initials.
eg
Field1                                                            Field2(Initials)
Fred Bloggs, Dave Smith, Eileen Bab            FB, DS, EB
John Sayer                                                   JS
etc etc
philsivyerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
sdstuberConnect With a Mentor Commented:
oops  try this instead

       REGEXP_REPLACE(
           REGEXP_REPLACE(REPLACE(field1, ' and ', ', '), '\([^)]*\)'),
           '([A-Z])[a-z ]*',
           '\1'
       )

I use Appendix C in the SQL Reference

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/ap_posix.htm#i691974

in 11g the SQL Reference moves the Regular Expressions to Appendix D
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
what is the output you need for that data intput ?
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
you mean field2 is the output you wanted ? do you need it as single sql query or pl/sql code block is also ok for you ?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Go Premium
 
philsivyerAuthor Commented:
Field 2 is the required output and preferably as single sql query
0
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
try this :
create or replace function f1(var varchar2) return varchar2
is
rr varchar2(100) :=null;
begin
rr:= substr(var,1,1);
for x in 1..length(var) loop
if ( substr(var,x,1) = ' ') then
rr := rr || substr(var,x+1,1);
elsif ( substr(var,x,1) = ',') then
rr := rr || substr(var,x,1);
end if;
end loop;
return rr;
end;
/

select name1, f1(name1) from test1;

This works for me as you wanted.

But this can also be done with single sql with or without regular expressions.  

Thanks
0
 
philsivyerAuthor Commented:
I'd be intrigued with single sql solution
Regards
0
 
sdstuberCommented:
10g and above, try this...

regexp_replace(field1,'([A-Z])[a-z ]*','\1')
0
 
philsivyerAuthor Commented:
The regex looks interesting- what if in the string we also have something like ....

Field 1                                                                                      Required Output
Fred Bloggs, Dave Smith (Instructor), Eileen Bab                    FB, DS, EB
Phil Smith and Alan Vine, John Brown                                      PS , AV, JB


So, I would like to leave out "and" and anything in brackets.

Regards
0
 
sdstuberCommented:
you'll have to nest replacements


 REGEXP_REPLACE(
           REGEXP_REPLACE(REPLACE(field1, ' and ', ', '), '\(.*\)'),
           '([A-Z])[a-z ]*',
           '\1'
       )
0
 
philsivyerAuthor Commented:
This looks good - one small problem is that if I get more than one set of brackets it leaves out
other required data - eg

Fred Bloggs, Dave Smith (Instructor), Eileen Bab (Head of Dept) it returns FB, DS.

Any good sites re regex and oracle?  
0
 
philsivyerAuthor Commented:
Thanks - really good
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.