Dataset looks like this

I would want "Banana" returned from the first row and "Chips" returned from the second row.

I've done (instr (Column B, ' ', -2)

This gives me the location of the space

I know I need to use a substr in conjunction with this, but for substr I need to specify the length of characters I want to retrieve right? How do I overcome the problem where the length of characters after the last space is different each time?

CodePudding user response：

I know I need to use a substr in conjunction with this, but for substr I need to specify the length of characters I want to retrieve right?

No, if you do not specify the number of characters then the SUBSTR function will return the rest of the string.

So you can use:

SELECT SUBSTR(column_b, INSTR(column_b, ' ', -1)   1) AS last_word
FROM   table_name;

To get the substring after the last space.

Which, for the sample data:

CREATE TABLE table_name (Column_A, Column_B) AS
SELECT 'Apple', 'Apple Banana' FROM DUAL UNION ALL
SELECT 'Corn',  'Corn Chips'   FROM DUAL;

Outputs:

fiddle

CodePudding user response：

A simple option is to use regular expression, by fetching one or more occurrences of a word \w which is anchored to the end $ of the string.

Sample data:

SQL> with test (cola, colb) as
  2    (select 'apple', 'apple banana' from dual union all
  3     select 'corn' , 'corn chips'   from dual
  4    )

Query begins here:

  5  select cola, colb,
  6    --
  7    regexp_substr(colb, '\w $') last_word
  8  from test;

COLA  COLB         LAST_WORD
----- ------------ ------------
apple apple banana banana
corn  corn chips   chips

SQL>