substr: how to exclude special characters like } " _ etc

This is the message in the REMARKS column of my table

{"StatusCode":"0","StatusDescription":"","message":"","transactionid":"404897688","enddate":"04/03/2017","formula":"ACCESS"}_SUBS
{"StatusCode":"0","StatusDescription":"","message":"","transactionid":"404894098","enddate":"04/03/2017","formula":"EVASION"}_SUBN
{"StatusCode":"0","StatusDescription":"","message":"","transactionid":"404889188","enddate":"05/03/2017","formula":"LES CHAINES CANAL+"}_SUBS
{"StatusCode":"0","StatusDescription":"","message":"","transactionid":"404880515","enddate":"06/03/2017","formula":"EVASION+"}_SUBS

I am using this in my query

substr(remarks, (instr(remarks,'formula') + 10), 18) FORMULA

But i am also getting the special characters } " _ because EVASION+ LES CHAINES CANAL+ EVASION ACCESS are not of the same length.

Can someone explain how to exclude those special characters and get only the names displayed under FORMULA column.

thanks

Solution

Here is a solution using just the standard substr and instr functions (no regular expressions):

select substr( remarks, instr(remarks, '"formula":"') + 11,
               instr(substr(remarks, instr(remarks, '"formula":"') + 11), '"') - 1 )
from   inputs;