I'm looking to split a string in Teradata.
The table might look something like this.
column1
hello:goodbye:afternoon
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. [3707] 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.
If this was TD14 you wouldn't need INSTR
, there's a STRTOK
function :-)
STRTOK(column1,':',2),
For earlier releases it's
CASE
WHEN column1 LIKE '%:%:%'
THEN SUBSTRING(column1 FROM POSITION(':' IN column1) + 1 FOR POSITION(':' IN
SUBSTRING(column1 FROM POSITION(':' IN column1) + 1)) - 1)
WHEN column1 LIKE '%:%'
THEN SUBSTRING(column1 FROM POSITION(':' IN column1) + 1)
END
The CASE LIKE
ist just to prevent an "string subscript out of bound" error when there no colon.