Extract string after character and before final full stop (/period) in SQL

Question

I want to extract all of the string after, and including, 'Th' in a string of text (column called 'COL_A' and before, and including, the final full stop (period). So if the string is:

'3padsa1st/The elephant sat by house No.11, London Street.sadsa129'

I want it to return:

'The elephant sat by house No.11, London Street.'

At the moment I have:

substr(SUBSTR(COL_A, INSTR(COL_A,'Th', 1, 1)),1,instr(SUBSTR(COL_A, INSTR(COL_A,'Th', 1, 1)),'.'))

This nearly works but returns the text after and including 'Th' (which is right), but returns the text before the first full stop (period), rather than the final one. So it returns:

The elephant sat by house No.

Thanks in advance for any help!

How can you decide which is the real full stop? In the example, how can you say that the stop is after "London Street." and not after "No."? Is the real full stop given by the last period in the string? — Aleksej, Mar 15 '16 at 15:49
You know that if you INSTR(x,'.',-1) it will search backwards for the first period counting back from the end of the data? — Michael Broughton, Mar 15 '16 at 15:53

score 2 · Accepted Answer · answered Mar 15 '16 at 15:53

2

Assuming that the full stop is given by the last period in you string, you can try with something like this:

select regexp_substr('3padsa1st/The elephant sat by house No.11, London Street.sadsa129', 
                     '(Th.*)\.')
from dual

answered Mar 15 '16 at 15:53

Aleksej

21,858
5
28
36

So much cleaner than the ghastly nested SUBSTR()/INSTR()! – Gary_W Mar 15 '16 at 16:00

score 2 · Answer 2 · answered Mar 15 '16 at 15:59

From the INSTR docs, you can use a negative value of position to search backwards from the end of the string, so this returns the position of the last full stop:

instr (cola, '.', -1)

So you can do this:

substr ( cola
       , instr (cola, 'Th')
       , instr (cola, '.', -1) - instr(cola, 'Th') + 1
       )

score 0 · Answer 3 · edited May 23 '17 at 11:45

0

Your code is returning the position of the first period in the string. If you want it to return the position of the last period in the string try looking into reversing the string and then using instr.

I'm not familiar with Oracle but to give you an idea have a look at this previously answered question for SQL Server.

What is best way to get last indexof character in SQL 2008

edited May 23 '17 at 11:45

Community

1
1

answered Mar 15 '16 at 15:50

Simon

1,117
4
15
33

You don't need to reverse the string. If you use a negative number for the "occurance" parameter to INSTR() it will search backwards from the end of the string. – Michael Broughton Mar 15 '16 at 15:54
Wow thats pretty neat. For those who would like to look it up: https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_1103.htm – Simon Mar 15 '16 at 15:57

score 0 · Answer 4 · edited Mar 15 '16 at 16:42

0

instr('string', 'char', -1)

This would search for a char in the string from end because of the position -1. So you can use a similar code to search for '.' from the end.

substr(SUBSTR(COL_A, INSTR(COL_A,'Th', 1, 1)),1,instr(SUBSTR(COL_A, INSTR(COL_A,'Th', 1, 1)),'.', -1))

edited Mar 15 '16 at 16:42

Ben Hoffman

7,769
6
41
69

answered Mar 15 '16 at 16:19

Avani

177
6

Extract string after character and before final full stop (/period) in SQL

4 Answers4