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
ralph_reaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
sdstuberConnect With a Mentor Commented:
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
 
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
All Courses

From novice to tech pro — start learning today.