I'm trying to use SUBSTRING and INSTR to extract specific words. So, say I want to select "goodbye". I'm trying the following query.
SELECT SUBSTRING(a.column1 from index(a.column1,':')+1 for INSTR(a.column1,':',0,2))
FROM db.table as a
I get the following error.
SELECT Failed.  Syntax error, expected something like ')' between the word 'INSTR' and '('
I'm not sure why I'm getting that error. It lets me use INDEX to deduce a number in place of INSTR, so I'm not sure why it is acting this way when I use INSTR.
Have a look at regexp_substr example below:
select regexp_substr('hello:good:afternoon','[^:]+',1,1) val;
select regexp_substr('hello:good:afternoon','[^:]+',1,2) val;
select regexp_substr('hello:good:afternoon','[^:]+',1,3) val;
You can try this way,if you are using <14 version.