ramon fincken
Site's programmer
Get a free globally recognized avatar
It's free!
Joined: 03 Aug 2007
Posts: 405
Location: A'dam/Diemen, The Netherlands
|
 Posted: Thu Mar 18, 2021 9:28 am Post subject: Split colum item in multiple colums |
 |
|
Split column item in multiple columns, and fill with empty if field Quantity is less then the split
Split on ; character.
The coupons column is " coupon1;coupon2 " etc .. and it needs to be split for the export we need.
| Code: | SELECT id, `Quantity`, `email`, coupons
,SUBSTRING_INDEX(coupons,';',1) 'coupon1'
,IF(Quantity>1, SUBSTRING_INDEX(SUBSTRING_INDEX(coupons,';',2),';',-1) , '') 'coupon2'
,IF(Quantity>2, SUBSTRING_INDEX(SUBSTRING_INDEX(coupons,';',3),';',-1) , '') 'coupon3'
,IF(Quantity>3, SUBSTRING_INDEX(SUBSTRING_INDEX(coupons,';',4),';',-1) , '') 'coupon4'
,IF(Quantity>4, SUBSTRING_INDEX(SUBSTRING_INDEX(coupons,';',5),';',-1) , '') 'coupon5'
,IF(Quantity>5, SUBSTRING_INDEX(SUBSTRING_INDEX(coupons,';',6),';',-1) , '') 'coupon6'
,IF(Quantity>6, SUBSTRING_INDEX(SUBSTRING_INDEX(coupons,';',7),';',-1) , '') 'coupon7'
,IF(Quantity>7, SUBSTRING_INDEX(SUBSTRING_INDEX(coupons,';',8),';',-1) , '') 'coupon8'
,IF(Quantity>8, SUBSTRING_INDEX(SUBSTRING_INDEX(coupons,';',9),';',-1) , '') 'coupon9'
,IF(Quantity>9, SUBSTRING_INDEX(SUBSTRING_INDEX(coupons,';',10),';',-1) , '') 'coupon10'
FROM `coupons` |
|
|
