I would be needing your help here. I have data coming into a column as A|B|C|D| which I would be needing to split into columns based on pipe delimiter. Now the number of Pipes is unknown so if D is not present, I have to populate the value of C and if C is unknown then I would have to use B and so on. Unfortunately, the level is not unknown so if the string doesnt have all the values of A, B, C and D present, one column could contain the value of A,B,C,D as a merge. Could you please help me with this? Thanks in advance
I still don't get what you wane achive
These are quite usefull - for you the list functions might be of interesst...
I can understand :) and apologies on being unclear. Let me try to elaborate it. I have a String Dataset coming in a column, PIPE delimited. There are multiple rows forming the data into the set of columns. Now this Data should be parsed into columns based on the pipe delimiter. If I take:
then COL1 would be A, COL2 would be B, COL3 would be C and so on. The Last Delimiter should not be considered as it does not contain any value.
In case 2, We only have to Parse until D
I hope this helps understanding my problem
Consider the udfs you could directly use them to extract your infos.
Otherwise - I guess A,B,C etc are examples. So these strings can be of variable lenght?
How long is your source column? Varchar(X)?
Yes, youre right. My output should look like the one you mentioned
Strings are of variable Length. My source column is varchar 255.
Can we do it with Recursive?
Here you go... LEFT_WORD in resultset is youre desired output.
DROP TABLE T1;
CREATE TABLE T1
row_id INT NOT NULL,
text VARCHAR(991) NOT NULL
INSERT INTO T1 VALUES (1,'A|B|C|D|E');
WITH RECURSIVE recursive_T1
) AS (
POSITION('|' IN text || '|') AS len,
SUBSTRING(text FROM 1 FOR len) AS left_word,
SUBSTRING(text || '|' FROM len+1) AS right_word,
1 as pos
POSITION('|' IN right_word) AS len_new,
left_word || SUBSTRING(right_word FROM 1 FOR len_new) as left_word,
SUBSTRING(right_word FROM len_new + 1) as right_word,
pos + 1 as pos
WHERE right_word <> ''