Solved

Oracle SQL - Extract rightmost word in string

Posted on 2014-01-27
8
924 Views
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"

Thanks.
0
Comment
Question by:iamnamja
[X]
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
8 Comments
 
LVL 74

Expert Comment

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


for example...


select regexp_substr('Administration Main Boston','[^ ]+$') from dual
0
 
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;
0
 
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;
0
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

by:iamnamja
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.
0
 
LVL 74

Accepted Solution

by:
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','[^ ]+ *[^ ]+$'),'[^ ]+')
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39813010
another interesting variation for 2nd last word

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

Expert Comment

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

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: http://www.e-e.com/A_8429.html 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