Improve company productivity with a Business Account.Sign Up

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

Oracle query

I've this table: TAB_USERS with column USER (name last + first name)

USER
Smith John
Smith David
Smith Jack
Di Martino Giovanni
De Nicola Antonio

I'd like to write a query with only upper (first name).

I tried this query:

select UPPER(substr (USER, instr(USER, ' ')+1))
from TAB_USERS;

But I've some problem with the names: Di Martino Giovanni and De Nicola Antonio

How can I write my query also considering the names with two spaces?

I'd like to get this output:

USER
JOHN
DAVID
JACK
GIOVANNI
ANTONIO
0
ralph_rea
Asked:
ralph_rea
  • 4
1 Solution
 
sdstuberCommented:
select UPPER(regexp_substr(USER, '[^ ]+$')) from TAB_USERS;

0
 
sdstuberCommented:
select UPPER(substr (USER, instr(USER, ' ',-1)+1))
from TAB_USERS;

0
 
ralph_reaAuthor Commented:
Ok, thanks bu now I've another problem, there are some users with this name: Smith John (USA)

How Can I avoid this problem??
0
 
sdstuberCommented:
>> now I've another problem


select UPPER(regexp_substr(replace(USER,'(USA)'), '[^ ]$')) from TAB_USERS;


if you need something more sophisticated than that...

please open a new question,  list all the special parsing rules you want and how to identify them
0
 
sdstuberCommented:
sorry,  I missed some characters above...

 UPPER(regexp_substr(replace(usr,' (USA)'), '[^ ]+$'))

or, using the instr method...

UPPER(SUBSTR(REPLACE(usr, ' (USA)'), INSTR(REPLACE(usr, ' (USA)'), ' ', -1) + 1))
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.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

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