Split colum item in multiple colums


min read

My profile

Share this:

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:1:1b5d6bdfcd]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` [/code:1:1b5d6bdfcd]

Share this:

Leave a Reply

Your email address will not be published. Required fields are marked *