Oracle SQL - Extract rightmost word in string

Hi, I need to extract the right-most word from a field that could have multiple spaces. Example: "Administration Main Boston" = "Boston"

Thanks.
iamnamjaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
sdstuberConnect With a Mentor Commented:
regexp_substr('Administration Main Boston Office','([^ ]+) *[^ ]+$',1,1,null,1)

or, if your version doesn't support substr back references


regexp_substr(regexp_substr('Administration Main Boston Office','[^ ]+ *[^ ]+$'),'[^ ]+')
0
 
sdstuberCommented:
regexp_substr(yourstring,'[^ ]+$')


for example...


select regexp_substr('Administration Main Boston','[^ ]+$') from dual
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Several ways.

Try this one:
select regexp_substr('administration main boston','[^ ]+$') from dual;
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
slightwv (䄆 Netminder) Commented:
substr/instr method:
select substr('administration main boston', instr('administration main boston',' ',-1)+1) from dual;
0
 
iamnamjaAuthor Commented:
Thank you both so much for the function.
One more question: what if I need to extract 2nd right most word? Just realized, I need to do this for some conditions. Is there a quick way to extract 2nd right most word?

"Administration Main Boston Office" = "Boston"

Thank you.
0
 
sdstuberCommented:
another interesting variation for 2nd last word

reverse(regexp_substr(reverse('Administration Main Boston Office'),'[^ ]+',1,2))
0
 
sdstuberCommented:
you should awared a split with slightwv as well for answering the original question
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.