4

I need this to be done in Oracle SQL (10gR2). But I guess, I would rather put it plainly, any good, efficient algorithm is fine.

Given a line (or sentence, containing one or many words, English), how will you find the last word of the sentence?

Here is what I have tried in SQL. But, I would like to see an efficient way of doing this.

select reverse(substr(reverse(&p_word_in)
                         , 0
                         , instr(reverse(&p_word_in), ' ')
                         )
                  )
      from dual;

The idea was to reverse the string, find the first occurring space, retrieve the substring and reverse the string. Is it quite efficient? Is a regular expression available? I am on Oracle 10g R2. But I dont mind seeing any attempt in other programming language, I wont mind writing a PL/SQL function if need be.

Update:

Jeffery Kemp has given a wonderful answer. This works perfectly.

Answer

SELECT SUBSTR(&sentence, INSTR(&sentence,' ',-1) + 1)
FROM dual
Community
  • 1
  • 1
Guru
  • 2,331
  • 6
  • 31
  • 48
  • Ok, fine. A related question: http://stackoverflow.com/questions/3648133/regular-expression-to-find-last-word-in-sentence – Guru Oct 11 '10 at 18:35

6 Answers6

9

I reckon it's simpler with INSTR/SUBSTR:

WITH q AS (SELECT 'abc def ghi' AS sentence FROM DUAL)
SELECT SUBSTR(sentence, INSTR(sentence,' ',-1) + 1)
FROM q;
Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
2

Not sure how it is performance wise, but this should do it:

select regexp_substr(&p_word_in, '\S+$') from dual;
Wolph
  • 78,177
  • 11
  • 137
  • 148
  • This won't work if there is a period or other character at the end of the sentance, same goes for compie. – Jesse Oct 11 '10 at 18:41
  • @Jesse: true, perhaps every non-whitespace character would be a better solution – Wolph Oct 11 '10 at 18:42
  • 1
    select regexp_substr(&p_word_in, '[^\s]+$') from dual; or select regexp_substr(&p_word_in, '(\w+)[^\w]*$') from dual; should both work. The first will grab the period, the 2nd shouldn't (I'm not sure how parentheses are handled in oracle.) – Jesse Oct 11 '10 at 18:44
  • @Jesse: for the record, `[^\s]` is the same as `\S` and `[^\w]` is the same as `\W` :) – Wolph Oct 11 '10 at 18:57
1

I'm not sure if you can use a regex in oracle, but wouldn't

(\w+)\W*$

work?

Jesse
  • 10,370
  • 10
  • 62
  • 81
0

This regex matches the last word on a line:

\w+$

And RegexBuddy gives this code for use in Oracle:

DECLARE
        match VARCHAR2(255);
BEGIN
        match := REGEXP_SUBSTR(subject, '[[:alnum:]]_+$', 1, 1, 'c');
END;
compie
  • 10,135
  • 15
  • 54
  • 78
0

this leaves the punctuation but gets the final word

with datam as (
SELECT 'abc asdb.' A FROM DUAL UNION
select 'ipso factum' a from dual union
select 'ipso factum' a from dual union
SELECT 'ipso factum2' A FROM DUAL UNION
SELECT 'ipso factum!' A FROM DUAL UNION
SELECT 'ipso factum  !' A FROM DUAL UNION
SELECT 'ipso factum/**//*/?.?' A FROM DUAL UNION
SELECT 'ipso factum  ...??!?!**' A FROM DUAL UNION
select 'ipso factum ..d.../.>' a from dual 

)
SELECT a,
--REGEXP_SUBSTR(A, '[[:alnum:]]_+$', 1, 1, 'c') , /** these are the other examples*/
--REGEXP_SUBSTR(A, '\S+$') ,  /** these are the other examples*/
regexp_substr(a, '[a-zA-Z]+[^a-zA-Z]*$')  

from datam
Harrison
  • 8,970
  • 1
  • 32
  • 28
0

This works too, even for non english words :

SELECT REGEXP_SUBSTR ('San maria Calle Cáceres Numéro 25 principal izquierda, España', '[^ .]+$') FROM DUAL;
Moussa
  • 1
  • 2