CODEWITHSUNDEEP
sqlstringoracle-databaseplsql
jedge
jedge

Reputation: 1047

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

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!

Upvotes: 2

Views: 7241

Answers (4)

Avani
Avani

Reputation: 215

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))

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132570

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
       )

Upvotes: 2

Aleksej
Aleksej

Reputation: 22949

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

Upvotes: 2

Simon
Simon

Reputation: 1333

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

Upvotes: 0

Related Questions