Splitting values on comma in MySQL for PayPal CSV

1.) make a numbers table

CREATE TABLE IF NOT EXISTS `numbers` (
`N` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `numbers` (`N`) VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9),
(10);

 

2.)

select
Date, Gross, Fee, Net, Transaction_ID,
substring_index(
substring_index(Item_ID, ',', n),
',',
-1
) as Item_ID, Country
from paypal
join numbers
on char_length(Item_ID)
- char_length(replace(Item_ID, ',', ''))
>= n - 1

 

3.) to find the count

SELECT Item_ID, COUNT(Item_ID) as count FROM paypal
GROUP BY Item_ID
ORDER BY count DESC

Leave a Reply

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