Help in Recursive query required

Database
Enthusiast

Help in Recursive query required

Hi,

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

Tags (1)
17 REPLIES
WAQ
Enthusiast

Re: Help in Recursive query required

Enthusiast

Re: Help in Recursive query required

Thanks WAQ for the prompt response. I wrote the same solution for this as well but doesnt solve my problem unfortunately :). My String is 5 PIPES long and I have to manipulate the data as if in ROW 1 I have All A, B, C, D, and E present I have to take each one and define levels over it. If in second Row I have A, B, C and D present, I have to take D and populate the resulting column with D and E from the right and so on. Please let me know if this is unclear. I try to illustrate it now.

A|B|C|D|E|

A|B|C|D|

A|B|C|

But one thing to remember is that we have to start parsing it from the right and the right most Pipe should not be considered :).

Thanks,

Senior Supporter

Re: Help in Recursive query required

I still don't get what you wane achive

But also check http://developer.teradata.com/blog/madmac/2010/03/a-few-basic-scalar-string-udfs

These are quite usefull - for you the list functions might be of interesst...

Enthusiast

Re: Help in Recursive query required

Hi Ulrich,

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:

A|B|C|D|E|

A|B|C|D|

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

Thanks,

WAQ
Enthusiast

Re: Help in Recursive query required

So your ouput for this string would be something like this?

1   A|B|C|D|E|

2   A|B|C|D|

3   A|B|C|

4   A|B|

5   A|

Senior Supporter

Re: Help in Recursive query required

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?

AAA|BB|CCCC|

?

How long is your source column? Varchar(X)?

Enthusiast

Re: Help in Recursive query required

Hi WAQ,

Yes, youre right. My output should look like the one you mentioned

Hi Ulrich,

Strings are of variable Length. My source column is varchar 255.

Can we do it with Recursive?

Thanks

Farhan

Enthusiast

Re: Help in Recursive query required

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
(row_id,
text,
len,
left_word,
right_word,
pos
) AS (
SELECT
row_id,
text,
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
FROM T1

UNION ALL

SELECT
row_id,
text,
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
FROM recursive_T1
WHERE right_word <> ''
)
SELECT *
FROM recursive_T1

Enthusiast

Re: Help in Recursive query required

You need to test it comprehensively as I can only help you with limited test cases :)