CODEWITHSUNDEEP
oracle-databaseplsql
Sigularity
Sigularity

Reputation: 967

How to get the second last word in a string?

I have a string like below, and I need to get 10472314 in it. It is the last word but one in this case. Can you let me know how to get it in PL/SQL block? Any string function?

processed "SCOTT"."PRINCE05"                         10472314 rows

Upvotes: 1

Views: 7100

Answers (3)

Steffen Scheibler
Steffen Scheibler

Reputation: 21

This gets you the last TWO words in a string and then shows you the first word of those two words which will always be the second-last word:

SUBSTRING_INDEX(SUBSTRING_INDEX(, " ", -2), ' ', 1)

Upvotes: 1

William Robertson
William Robertson

Reputation: 16001

The set of digits before the word 'rows' at the end of the string:

regexp_replace(text, '^(.+ )([0-9]+)( rows)$', '\2')

The third word:

regexp_substr(text, '\S+', 1, 3)

The second last word (the nth word where n = the number of words -1):

regexp_substr(text, '\S+', 1, regexp_count(text,'\S+') -1)

If you are processing a significant number of rows then the regex functions can be slow. The trade-off you have to make is between the expressiveness of regular expressions and the performance of plain substr and instr. Personally I prefer regexes unless there is a clear performance issue.

Upvotes: 3

Sigularity
Sigularity

Reputation: 967

If we use a '-1' magic option in INSTR then we can search the string in reverse order. By doing that, I believe we can find the last word but one like below. It looks dirty but it just works for me.

SELECT SUBSTR( name, INSTR( name, ' ', -1 , 2 ) + 1 , INSTR( name, ' ', -1 , 1 ) - INSTR( name, ' ', -1 , 2 ) ) 
FROM prince_test;

INSTR( name, ' ', -1 , 2 ) + 1  ==> the second occurrence of SPACE
INSTR( name, ' ', -1 , 1 ) - INSTR( name, ' ', -1 , 2 )  ==> position gap between the first occurrence and the second.

Upvotes: 0

Related Questions