0

Column actions_serialized contains the following value:

a:7:{s:4:"type";s:40:"salesrule/rule_condition_product_combine";s:9:"attribute";N;s:8:"operator";N;s:5:"value";s:1:"1";s:18:"is_value_processed";N;s:10:"aggregator";s:3:"any";s:10:"conditions";a:3:{i:0;a:5:{s:4:"type";s:32:"salesrule/rule_condition_product";s:9:"attribute";s:3:"sku";s:8:"operator";s:2:"==";s:5:"value";s:17:"SKU-ABC1";s:18:"is_value_processed";b:0;}i:1;a:5:{s:4:"type";s:32:"salesrule/rule_condition_product";s:9:"attribute";s:3:"sku";s:8:"operator";s:2:"==";s:5:"value";s:76:"SKU-ABC203";s:18:"is_value_processed";b:0;}i:2;a:5:}}}

Now I need to fetch all SKUs in a separate line.

So I have the following query but it only returns the first instance of the SKU occurrence.

How do I get all other occurrences, and then how would I split them into a separate line?

Note, SKU occurrences may sometimes add up to 10+ SKUs.

select 
sr.id, 
 group_concat(distinct substring(sr.actions_serialized, 
 locate('sku', sr.actions_serialized)+47,
 locate(';s:18:"is_value_processed";b', sr.actions_serialized)-
 locate('sku', sr.actions_serialized)-48)) skus 

from from salesrule sr
group by sr.id

Above query returns only first instance of SKU. But desired results are:

id   skus
1    SKU-ABC1
1    SKU-ABC203

If not possible, then at least:

id   skus
1    SKU-ABC1,SKU-ABC203
joell
  • 71
  • 1
  • 4
  • 18

1 Answers1

0

This should work with CONCAT. 

SELECT 
  sr.id, 
  CONCAT(distinct substring(sr.actions_serialized, 
  locate('sku', sr.actions_serialized)+47,
  locate(';s:18:"is_value_processed";b', sr.actions_serialized)-
  locate('sku', sr.actions_serialized)-48)) AS skus 
FROM salesrule sr
GROUP BY sr.id
thomasw_lrd
  • 534
  • 3
  • 13
  • Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct substring(sr.actions_serialized, locate('sku', sr.actions_serialized' at line 3 – joell Mar 08 '16 at 01:00