All Forums Database
cloud36 5 posts Joined 04/14
13 May 2014
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.

Raja_KT 1246 posts Joined 07/09
13 May 2014

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;

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

KVB 124 posts Joined 09/12
14 May 2014

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)

Langaliya.Nirav 15 posts Joined 04/11
08 Nov 2014

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 ;

 

 

--Nirav Langaliya

You must sign in to leave a comment.