Archives of the TeradataForum

Message Posted: Tue, 07 Dec 2004 @ 12:37:38 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Jebaseelan,

I think we can use Index, substring and concat function together to achieve the desired output.

Please see the code for replacing undesired '|' character from one of the columns. Here 4 '|' instances has been checked.

Please see and let me know if it can be of any use for you.

     select
     CASE WHEN index_1 > 0
     THEN  SUBSTR(TABLE_NAME.COL_NAME,1, index_1-1)
     ELSE SUBSTR(TABLE_NAME.COL_NAME,1, index_1+1-1)
     END AS  Word_1

     ,CASE WHEN index_2-index_1 > 0
     THEN SUBSTR(TABLE_NAME.COL_NAME, index_1+1, index_2-index_1-1)
     ELSE SUBSTR(TABLE_NAME.COL_NAME, index_1+1, index_2-index_1-1+1)
     END As Word_2

     ,CASE WHEN index_3 = index_2
     THEN substr(TABLE_NAME.COL_NAME, index_2+1)
     ELSE substr(TABLE_NAME.COL_NAME, index_2+1, index_3-index_2-1)
     END As Word_3

     ,CASE WHEN index_3 = index_2 THEN ''
     WHEN index_4 = index_3
     THEN substr(TABLE_NAME.COL_NAME, index_3+1)
     ELSE substr(TABLE_NAME.COL_NAME, index_3+1, index_4-index_3-1)
     END As Word_4

     ,index(TABLE_NAME.COL_NAME,'|') as Index_1
     ,index(substr(TABLE_NAME.COL_NAME, index_1+1), '|') + Index_1 As Index_2

     ,index(substr(TABLE_NAME.COL_NAME, index_2+1), '|') + Index_2 As Index_3

     ,index(substr(TABLE_NAME.COL_NAME, index_3+1), '|') + Index_3 As Index_4

     ,TRIM(Word_1||' '||Word_2||' '||Word_3||' '||Word_4) as "Column_name"

     From.......

Thanks,

-Rajiv

	<Prev	Next>		<<First	<Prev	Next>	Last>>