Help in Recursive query required

Database
Enthusiast

Re: Help in Recursive query required

Hi QAKiani,

Thanks very much, quite near to what I am looking for :). Can we Split the String in columns and start parsing from the Right instead of the left? For instance, If I get A|B|C|D|E| in the column, I split the data in multiple columns like:

A|B|C|D|E|

A|B|C|D|

A|B|C|

COL1 COL2 COL3 COL4 COL5

A          B      C       D       E

A          B      C       D

A          B      C

and so on.

One thing what is in our favor is this string could only get upto E so we can hardcode the columns to five and populate empty column when the value is not present, except the last pipe from the right. There are multiple rows coming into the data like what I have shared above.

Perhaps I am creating confusion instead of clarification, my bad. I am at a Duel with the person who wrote the rule :)

Thanks very much guys the help in advance

Enthusiast

Re: Help in Recursive query required

Hi Farhan,

Yes you are creating the confusion just like good old day :)

The solution posted earlier solves the problem that was mentioned initially. Re parsing from the right, that is not possible in simple SQL because the SQL function POSITION finds the position of first instance of the substring in the actual string. Parsing from right could be done through a recursive sql itself, but I don't think you will need that.

Now coming to the new requirement, if you are putting all the substring in the columns, does the column sequence matter or it could be in any order?

--Qaisar

Enthusiast

Re: Help in Recursive query required

Farhan,

The below SQL will return the 5 columns in one row. Each column has the content between two pipes. Is this what you need?

Have fun...

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-1) 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,
SUBSTRING(right_word FROM 1 FOR len_new-1) as left_word,
SUBSTRING(right_word FROM len_new + 1) as right_word,
pos + 1 as pos
FROM recursive_T1
WHERE right_word <> ''
)
SELECT text, max(case when pos=1 then left_word else null end )as col1
, max(case when pos=2 then left_word else null end ) as col2
, max(case when pos=3 then left_word else null end ) as col3
, max(case when pos=4 then left_word else null end ) as col4
, max(case when pos=5 then left_word else null end ) as col5
from recursive_t1
group by 1

-- Qaisar

Senior Supporter

Re: Help in Recursive query required

alternative  code avoiding recurisve query

CREATE volatile TABLE T1
(
row_id INT NOT NULL,
text VARCHAR(991) NOT NULL
) unique primary index (row_id)
on commit preserve rows;

INSERT INTO T1 VALUES (1,'A|B|C|D|E|');
INSERT INTO T1 VALUES (2,'AA|BB|CC|DD|');
INSERT INTO T1 VALUES (3,'AAA|BBB|CCC|');
INSERT INTO T1 VALUES (4,'AAaa|BBbb|');
INSERT INTO T1 VALUES (5,'AAaaa|');

select row_id,
col1,
col2,
col3,
col4,
case when index(text,'|')>0 then substr(text,1,index(text,'|')-1) else null end as col5
from
(
select row_id,
col1,
col2,
col3,
case when index(text,'|')>0 then substr(text,1,index(text,'|')-1) else null end as col4,
case when index(text,'|')>0 then substr(text,index(text,'|')+1) else null end as text

from
(

select row_id,
col1,
col2,
case when index(text,'|')>0 then substr(text,1,index(text,'|')-1) else null end as col3,
case when index(text,'|')>0 then substr(text,index(text,'|')+1) else null end as text

from
(
select row_id,
col1,
case when index(text,'|')>0 then substr(text,1,index(text,'|')-1) else null end as col2,
case when index(text,'|')>0 then substr(text,index(text,'|')+1) else null end as text
from
(
select row_id,
case when index(text,'|')>0 then substr(text,1,index(text,'|')-1) else null end as col1,
case when index(text,'|')>0 then substr(text,index(text,'|')+1) else null end as text
from t1
) as tmp1
) as tmp2
) as tmp3
) as tmp4
order by 1

Enthusiast

Re: Help in Recursive query required

Hello Qaiser,

Hehe :). Just reconfirmed that sequence of columns is required. 'A' becomes COL A value, 'B' as COL B value, 'C' as COL 'C' and so on and we have to start parsing from the right and we shall pick the column of interest upon need as this Hierarchy data. I understand that this is only possible thru Recursive query but since I have no experience with it, I need help. Hope this time I am clear :)

Thanks in advance

Enthusiast

Re: Help in Recursive query required

Farhan,

Does the SQL I posted few minutes ago meets your requirements? If not then whats missing?

-- Qaisar

Enthusiast

Re: Help in Recursive query required

Qaiser,

It worked. Thanks very much for the help :).

Thanks very much to all who helped :).

Farhan

Enthusiast

Re: Help in Recursive query required

Goood... Enjoy!! :P

-- Qaisar