1

I have a string (with spaces) from which i want to separate the last word. For example:

"Steve Jobs" => Jobs
"Stack OverFlow Question" => Questions

Is there any function in PL/SQL or SQL so that i am able to get the results as Jobs and Question separated out?

ean5533
  • 8,884
  • 3
  • 40
  • 64
Femme Fatale
  • 870
  • 7
  • 27
  • 56
  • Similar to http://stackoverflow.com/questions/7241090/ansi-sql-92-find-last-occurrence-of-character And I could get you a solution but it unfortunately would require the use of `REVERSE` which I believe is non-standard. – Yuck Dec 08 '11 at 18:29
  • @Sparky The plsql tag implies Oracle. – ean5533 Dec 08 '11 at 18:35
  • Thanks, I wasn't sure, because some older versions of Microsoft SQL used to be called PL/SQL... Thanks for adding the tag... – Sparky Dec 08 '11 at 18:37

2 Answers2

5

You can use INSTR and SUBSTR. INSTR tells you the position of a particular character. Passing a -1 for the start position tells Oracle to start looking at the end of the string backwards toward the front of the string.

SQL> ed
Wrote file afiedt.buf

  1  with x as (
  2    select 'Steve Jobs' str from dual union all
  3    select 'Stack Overflow Question' from dual
  4  )
  5  select substr( str, instr( str, ' ', -1 ) + 1 ) last_word
  6*   from x
SQL> /

LAST_WORD
----------------------------------------------------------------------  
Jobs
Question
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
0

Here is a way to do it in Microsoft SQL. Concept should work the same with string function changes to Oracle syntax.

declare @fldName varchar(30)
set @fldName = 'Steve Jobs'

select reverse(substring(reverse(@fldName),1,charindex(' ',reverse(@fldName))-1))

set @fldName = 'Stack Overflow Question'

select reverse(substring(reverse(@fldName),1,charindex(' ',reverse(@fldName))-1))
Sparky
  • 14,967
  • 2
  • 31
  • 45
  • Justin's answers is a much better solution, wasn't sure Oracle supported the -1 start position. Plus, it is a lot easier to read – Sparky Dec 08 '11 at 18:41