Exact strings in a column in SQL

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).

Leave a Reply

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