How to divide a record and show specific part of it

Question

I have table with 4 columns.

user_id,  user_name,  password,  full_name
706    ,  29682    ,  29682   ,  Nikolay Valeriev Rusanov
707    ,  30211    ,  30211   ,  Valentin Ognyanov Karamanchev

When i make the selection i need to show only the last name from the column full_name.

Query should return

user_id,  user_name,  password,  full_name
706    ,  29682    ,  29682   ,  Rusanov
707    ,  30211    ,  30211   ,  Karamanchev

They are separated with "space". I can't use substr, because every record in the column have different length and i have more than 10 000 rows.

Can u guys give me some sql query how to do it??

@Filburt show me were is the answer... – no reason Oct 08 '14 at 09:00 — no reason, Oct 08 '14 at 09:00

score 0 · Accepted Answer · answered Oct 08 '14 at 09:36

1. Using simple SUBSTR and INSTR :

SQL> WITH DATA AS(
  2  SELECT 'Nikolay Valeriev Rusanov' full_name FROM dual UNION ALL
  3  SELECT 'Valentin Ognyanov Karamanchev' FROM dual UNION ALL
  4  SELECT 'no reason' FROM dual)
  5  SELECT SUBSTR(full_name,instr(full_name,' ',-1)+1) last_name
  6  FROM DATA
  7  /

LAST_NAME
-----------------------------
Rusanov
Karamanchev
reason

SQL>

2. Using REGULAR EXPRESSION :

SQL> WITH DATA AS(
  2  SELECT 'Nikolay Valeriev Rusanov' full_name FROM dual UNION ALL
  3  SELECT 'Valentin Ognyanov Karamanchev' FROM dual UNION ALL
  4  SELECT 'no reason' FROM dual)
  5  SELECT regexp_substr (full_name, '\w*$') last_name
  6  FROM DATA
  7  /

LAST_NAME
-----------------------------
Rusanov
Karamanchev
reason

SQL>

Thanks alot, perfect answer! Appreciate a lot! – no reason Oct 08 '14 at 11:04 — no reason, Oct 08 '14 at 11:04
You can mark it answered. – Lalit Kumar B Oct 08 '14 at 11:07 — Lalit Kumar B, Oct 08 '14 at 11:07

How to divide a record and show specific part of it

1 Answers1