I am trying to use mysql to solve the question below.
Any idea how should I make it work? Thank you.
Tried to use the code below but extracted duplicate strings in two columns and it’s hard-code so it’s not working..
SELECT itemid, SUBSTRING_INDEX(SUBSTRING_INDEX(item_variation, ',', 1), ',', -1) 'type one',
SUBSTRING_INDEX(SUBSTRING_INDEX(item_variation, ',', 2), ',', -1) 'type two',
SUBSTRING_INDEX(SUBSTRING_INDEX(item_variation, ',', 3), ',', -1) 'type three',
SUBSTRING_INDEX(SUBSTRING_INDEX(item_variation, ',', 4), ',', -1) 'type four'
FROM data
Question:
To extract items with more than 3 types
|itemid|shopid|item_name|item_type|price|stock|creation_date|
|1|10000|clothes|{}|5|100|27/1/2018|
|2|10000|dress|{Pink: 20, Black: 20, Grey: 20}|20|100|20/2/2018|
|3|10001|t-shirt|{S: 2, M: 2, L: 2, XL: 2}|2|50|1/1/2018|
|4|10002|socks|{us5.5: 1, us9: 1, us4.5: 1, us10: 1, us7: 1, us6: 1, us5: 1}|1|1000|4/1/2018|
|5|10002|Gloves|{S: 2, M: 2}|2|500|6/1/2018|
Expected result
|itemid |item_name |item_type|
|3 |t-shirt |{S: 2, M: 2, L: 2, XL: 2}|
|4 |socks |{us5.5: 1, us9: 1, us4.5: 1, us10: 1, us7: 1, us6: 1, us5: 1}|
Answer
This ought to do:
select itemid, item_name, item_type from t where length(item_type) - length(replace(item_type, ',', '')) >= 3;
You need a special case to tell 0 and 1 apart. It would not work if item_type contains ‘,’ in either key or value of the json-like field (missing quoted around strings to be json).