6

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.

cloud36
  • 1,026
  • 6
  • 21
  • 35
  • Which version of Teradata are you on? I guess it was introduced only in TD 14 and if you are on an older version, then you need to get the UDF for the same... – visakh May 13 '14 at 19:38
  • Looks like I'm on 13.. How do I go about this operation then? – cloud36 May 13 '14 at 19:49
  • If you can get the INSTR UDF installed, that's the easiest option. Otherwise, you may have to tinker with POSITION...you can get the UDF's from here - https://downloads.teradata.com/download/extensibility/teradata-udfs-for-popular-oracle-functions – visakh May 14 '14 at 06:27

1 Answers1

14

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.

dnoeth
  • 59,503
  • 4
  • 39
  • 56