Multiple columns from one column containing multiple records

by tomas.trnka@keboola.com  

so far just internal notes, to be done


#1
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('1/2/3/4/5','/',1),'/',-1);  
#2
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('1/2/3/4/5','/',2),'/',-1);  
#3
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('1/2/3/4/5','/',3),'/',-1);  
#4
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('1/2/3/4/5','/',4),'/',-1);  
#5
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('1/2/3/4/5','/',5),'/',-1);  


SELECT (LENGTH('AVEED/FORTESTA Gel/AVEED/FORTESTA Gel') - LENGTH(REPLACE('AVEED/FORTESTA Gel/AVEED/FORTESTA Gel', '/', ''))) AS LENGTH;



SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('AVEED/FORTESTA Gel','/',-4),'/',1);


#1 product
UPDATE `subs` SET `Product_1` = `PRODUCT` WHERE (LENGTH(`PRODUCT`) - LENGTH(REPLACE(`PRODUCT`, '/', '')))=0;

#2 products
UPDATE `subs` SET `Product_1` = SUBSTRING_INDEX(SUBSTRING_INDEX(`PRODUCT`,'/',1),'/',-1)  WHERE (LENGTH(`PRODUCT`) - LENGTH(REPLACE(`PRODUCT`, '/', '')))=1;
UPDATE `subs` SET `Product_2` = SUBSTRING_INDEX(SUBSTRING_INDEX(`PRODUCT`,'/',2),'/',-1)  WHERE (LENGTH(`PRODUCT`) - LENGTH(REPLACE(`PRODUCT`, '/', '')))=1;

#3 products
UPDATE `subs` SET `Product_1` = SUBSTRING_INDEX(SUBSTRING_INDEX(`PRODUCT`,'/',1),'/',-1)  WHERE (LENGTH(`PRODUCT`) - LENGTH(REPLACE(`PRODUCT`, '/', '')))=2;
UPDATE `subs` SET `Product_2` = SUBSTRING_INDEX(SUBSTRING_INDEX(`PRODUCT`,'/',2),'/',-1)  WHERE (LENGTH(`PRODUCT`) - LENGTH(REPLACE(`PRODUCT`, '/', '')))=2;
UPDATE `subs` SET `Product_3` = SUBSTRING_INDEX(SUBSTRING_INDEX(`PRODUCT`,'/',3),'/',-1)  WHERE (LENGTH(`PRODUCT`) - LENGTH(REPLACE(`PRODUCT`, '/', '')))=2;

#4 products
UPDATE `subs` SET `Product_1` = SUBSTRING_INDEX(SUBSTRING_INDEX(`PRODUCT`,'/',1),'/',-1)  WHERE (LENGTH(`PRODUCT`) - LENGTH(REPLACE(`PRODUCT`, '/', '')))=3;
UPDATE `subs` SET `Product_2` = SUBSTRING_INDEX(SUBSTRING_INDEX(`PRODUCT`,'/',2),'/',-1)  WHERE (LENGTH(`PRODUCT`) - LENGTH(REPLACE(`PRODUCT`, '/', '')))=3;
UPDATE `subs` SET `Product_3` = SUBSTRING_INDEX(SUBSTRING_INDEX(`PRODUCT`,'/',3),'/',-1)  WHERE (LENGTH(`PRODUCT`) - LENGTH(REPLACE(`PRODUCT`, '/', '')))=3;
UPDATE `subs` SET `Product_4` = SUBSTRING_INDEX(SUBSTRING_INDEX(`PRODUCT`,'/',4),'/',-1)  WHERE (LENGTH(`PRODUCT`) - LENGTH(REPLACE(`PRODUCT`, '/', '')))=3;

#5 products
UPDATE `subs` SET `Product_1` = SUBSTRING_INDEX(SUBSTRING_INDEX(`PRODUCT`,'/',1),'/',-1)  WHERE (LENGTH(`PRODUCT`) - LENGTH(REPLACE(`PRODUCT`, '/', '')))=4;
UPDATE `subs` SET `Product_2` = SUBSTRING_INDEX(SUBSTRING_INDEX(`PRODUCT`,'/',2),'/',-1)  WHERE (LENGTH(`PRODUCT`) - LENGTH(REPLACE(`PRODUCT`, '/', '')))=4;
UPDATE `subs` SET `Product_3` = SUBSTRING_INDEX(SUBSTRING_INDEX(`PRODUCT`,'/',3),'/',-1)  WHERE (LENGTH(`PRODUCT`) - LENGTH(REPLACE(`PRODUCT`, '/', '')))=4;
UPDATE `subs` SET `Product_4` = SUBSTRING_INDEX(SUBSTRING_INDEX(`PRODUCT`,'/',4),'/',-1)  WHERE (LENGTH(`PRODUCT`) - LENGTH(REPLACE(`PRODUCT`, '/', '')))=4;
UPDATE `subs` SET `Product_5` = SUBSTRING_INDEX(SUBSTRING_INDEX(`PRODUCT`,'/',5),'/',-1)  WHERE (LENGTH(`PRODUCT`) - LENGTH(REPLACE(`PRODUCT`, '/', '')))=4;

Comments