Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1292
  • Last Modified:

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.
0
iamnamja
Asked:
iamnamja
  • 4
  • 2
2 Solutions
 
sdstuberCommented:
regexp_substr(yourstring,'[^ ]+$')


for example...


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

Try this one:
select regexp_substr('administration main boston','[^ ]+$') from dual;
0
 
slightwv (䄆 Netminder) Commented:
substr/instr method:
select substr('administration main boston', instr('administration main boston',' ',-1)+1) from dual;
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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:
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:
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now