All Forums Database
smithaa 23 posts Joined 08/09
04 May 2011
Inserting a value in table

I have column value like CUST.ONGOING |REVENUES Voice Monthly Fees-Contract. I want to extract data which is before pipe delimiter as one column and data after pipe delimiter as another column.
Can anyone please suggest which functions in the teradata can be used?

Jimm 298 posts Joined 09/07
04 May 2011

Substring and Position (or Substr and Index - they are the non ANSI versions):

Create Volatile Table T1
(PK Integer Not Null
, InCol Varchar(120) Not Null
Unique Primary Index (PK)
On Commit Preserve Rows

Insert Into T1 Values (1, 'CUST.ONGOING |REVENUES Voice Monthly Fees-Contract');

Select * FRom T1;

Select PK
, InCol
, Substring(InCol From 1 For Position('|' in InCol)-1)
, Substring(InCol From Position('|' in InCol)+1)
From T1

smithaa 23 posts Joined 08/09
17 May 2011

Thanks Jimm..This helped....Gr8

You must sign in to leave a comment.