Last word in a sentence: In SQL (regular expressions possible?)
Solution 1
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;
Solution 2
Not sure how it is performance wise, but this should do it:
select regexp_substr(&p_word_in, '\S+$') from dual;
Solution 3
I'm not sure if you can use a regex in oracle, but wouldn't
(\w+)\W*$
work?
Guru
I am an applied mathematics graduate. I work as a database professional. Software, I am interested in: Oracle, PL/SQL, Python, Ruby on Rails, Java, C, C++, Pascal, Prolog. Computers... Algorithms, Lot of Math, including Number Theory, Algebra and Graph Theory.
Updated on October 19, 2020Comments
-
Guru over 3 years
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