Oracle SQL - Extract rightmost word in string

Posted on 2014-01-27
Last Modified: 2014-01-27
Hi, I need to extract the right-most word from a field that could have multiple spaces. Example: "Administration Main Boston" = "Boston"

Question by:iamnamja
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
LVL 74

Expert Comment

ID: 39812966
regexp_substr(yourstring,'[^ ]+$')

for example...

select regexp_substr('Administration Main Boston','[^ ]+$') from dual
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 200 total points
ID: 39812967
Several ways.

Try this one:
select regexp_substr('administration main boston','[^ ]+$') from dual;
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39812976
substr/instr method:
select substr('administration main boston', instr('administration main boston',' ',-1)+1) from dual;
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 39812985
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.
LVL 74

Accepted Solution

sdstuber earned 300 total points
ID: 39813003
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','[^ ]+ *[^ ]+$'),'[^ ]+')
LVL 74

Expert Comment

ID: 39813010
another interesting variation for 2nd last word

reverse(regexp_substr(reverse('Administration Main Boston Office'),'[^ ]+',1,2))
LVL 74

Expert Comment

ID: 39813016
you should awared a split with slightwv as well for answering the original question

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Repeat query 13 74
grant user/role question 11 36
any step by steps guide on how to install Oracle 12c on Windows 10 8 105
capture vmstat info and insert it into an oracle table 31 58
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question