Split string and aggregate on split strings in sql-CodePudding

I have a sql column which holds multiple values delimited by ‘|’. I want to remove part of the sub string and aggregate ids again.

Select business_unit from issues will give the following now

one:123|two:456|three:567
one:223|three:378
three:458|nine:345

I want to modify these values and put them into another column of the table like below

123|456|567
223|378
458|345

I just want to remove the string before : after splitting by |

Please help with sql query.

CodePudding user response：

First you need to create split function that splits the string into array.

CREATE FUNCTION split(string TEXT, delimiter TEXT, n INT)
RETURNS TEXT DETERMINISTIC
RETURN IF(
     (LENGTH(string) - LENGTH(REPLACE(string, delimiter, ''))) / LENGTH(delimiter) < n - 1,
     NULL,
     SUBSTRING_INDEX(SUBSTRING_INDEX(string, delimiter, n), delimiter, -1)
);

Once you've created, you can make a use of it to get the result you expect using below sql query.

SELECT CONCAT(
    IFNULL(SUBSTRING_INDEX(split(business_unit, '|', 1), ':', -1),''), '|', 
    IFNULL(SUBSTRING_INDEX(split(business_unit, '|', 2), ':', -1),''), '|', 
    IFNULL(SUBSTRING_INDEX(split(business_unit, '|', 3), ':', -1),'')
     ) 
FROM issues;

CodePudding user response：

Perhaps I have misunderstood the issue, but you don't seem to need to split any strings or aggregate anything (or use PL/SQL) to get the result shown.

create table issues (business_unit) as
select 'one:123|two:456|three:567' from dual union all
select 'one:223|three:378' from dual union all
select 'three:458|nine:345' from dual;

select business_unit
     , regexp_replace(business_unit,'[^|]*:')
from   issues;

BUSINESS_UNIT             REGEXP_REPLACE(BUSINESS_UNIT,'[^|]*:')
------------------------- --------------------------------------
one:123|two:456|three:567 123|456|567
one:223|three:378         223|378
three:458|nine:345        458|345