My requirement is I want to convert a column into different rows based on a particular value(i.e. semicolon is the value as mentioned below).
I have a table with 2 two colums which has the records as mentioned below.
I want the result in following format.
I am trying to write a recursive query in teradata. The logic is as mentioned below.
With recursive recursive_table(col1,col2,col3) as (
col1 as col1
,SUBSTR(col2,1,INSTR(col2,';')-1) as col2
,SUBSTR(col2,INSTR(col2,';')+1,char(col2)) as col3
CASE when instr(col3,';') >0
end (varchar(25)) as col2
,CASE When instr(col3,';') >0
end as col3
where instr(col3,';') >=0
SEL col1,col2 from recursive_table;
But i am getting "Failed 6706: The string contains an untranslatable character error" error.
Can anyone please provide me the solution for this error with complete syntax.
seems like col2 is in Character Set Unicode and there's a cast to Latin.
What's your Teradata release? INSTR is built-in in TD14, earlier you might have an own UDF implementation limited to Latin.
Btw, in TD14 there's a new STRTOK_SPLIT_TO_TABLE function to do exactly what you want.
Thanks Dieter, its working now.
Thanks a lot for suggesting STRTOK_SPLIT_TO_TABLE function name. Its also working fine.