String Functions

Database

String Functions

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.


3 REPLIES

Re: String Functions

Have a look at regexp_substr example below:

REGEXP_SUBSTR:

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;

KVB
N/A

Re: String Functions

Hi

You can try this way,if you are using <14 version.

SEL SUBSTR('A:B:C',1,INSTR('A:B:C',':',1,1)-1),

SUBSTR('A:B:C',INSTR('A:B:C',':',1,1)+1,(LENGTH('A:B:C')-INSTR('A:B:C',':',1,2))),

SUBSTR('A:B:C',INSTR('A:B:C',':',1,2)+1)

Re: String Functions

Hi 

you can try this with TD14.0 version:

SEL 

CASE 

WHEN COL_1 = 'goodbye'  THEN COL_1

WHEN COL_2 = 'goodbye'   THEN COL_2

WHEN COL_3 ='goodbye'   THEN COL_3

END AS OUTPUT_STR

FROM

(

SEL 

'hello:goodbye:afternoon' AS INPUT_STR 

,STRTOK(INPUT_STR,':',1) AS COL_1

,STRTOK(INPUT_STR,':',2) AS COL_2

,STRTOK(INPUT_STR,':',3)AS COL_3

) A 

;


SEL 

CASE 

WHEN COL_1 = '<Require string>'  THEN COL_1

WHEN COL_2 = '<Require string>'  THEN COL_2

WHEN COL_3 ='<Require string>'   THEN COL_3

END AS OUTPUT_STR

FROM

(

SEL 

'hello:goodbye:afternoon' AS INPUT_STR 

,STRTOK(INPUT_STR,':',1) AS COL_1

,STRTOK(INPUT_STR,':',2) AS COL_2

,STRTOK(INPUT_STR,':',3)AS COL_3

) A ;