SQL regexp query on mysql

Question

I have 1 table named customer, there is a field named custom_id which contains related data.

{"18":["45","48","91"],"20":["82","83",84],"21":["30","31"]}

now I want to query the customer who has "20":["82","83","84"] in this field.

"20": this part what I want, and the related part my conditions (82,83,84)

the result will show if any customer has "82" or "83" or "84" after "20":

how can I query these conditions from this field using regexp.

here is the example:

SELECT * FROM `customer` WHERE `custom_id` REGEXP '"20":["82","83","84"]'

as you can see if I add [ ] system will not accept it like a string, and also these fields are (82, 83, 84) dynamic, anytime I can change it according to query.

thanks in advance.

You should be using MySQL's JSON support, not regex, here. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jan 5, 2018 at 14:07
Have your tried escaping the brackets i.e. '"20":\["82","83","84"\]' — user2417483
– user2417483, Commented Jan 5, 2018 at 14:17

astrangeloop · Accepted Answer · 2018-01-05 15:26:39Z

2

As Tim Biegeleisen said, you should be using MySQL's JSON support here if you can. One approach would be:

SELECT *
FROM customer
WHERE JSON_CONTAINS(custom_id, '"82"', '$."20"')
OR JSON_CONTAINS(custom_id, '"83"', '$."20"')
OR JSON_CONTAINS(custom_id, '"84"', '$."20"');

However, if your MySQL version doesn't support JSON, you could use REGEXP like this:

SELECT *
FROM customer
WHERE custom_id REGEXP '"20": ?\\[[^]]*[[:<:]]82[[:>:]]'
OR custom_id REGEXP '"20": ?\\[[^]]*[[:<:]]83[[:>:]]'
OR custom_id REGEXP '"20": ?\\[[^]]*[[:<:]]84[[:>:]]';

This regex will match the numbers in any order. It breaks down as follows:

"20": ? - literal "20": optionally followed by a single space
\\[     - opening [
[^]]*   - zero or more of anything other than a closing ]
[[:<:]] - opening word boundary (make sure we match entire number)
82      - number to match
[[:>:]] - closing word boundary

edited Jan 5, 2018 at 15:26

answered Jan 5, 2018 at 15:19

astrangeloop

1,5302 gold badges13 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nikos Tavoularis · Accepted Answer · 2018-01-05 15:50:11Z

0

I tried the following:

SELECT * FROM customer where custom_id regexp '[."20":["82","83","84"].]';

and it returned:

"20":["82","83","84"]

Check this link for the solution: http://sqlfiddle.com/#!9/d10fd6/3

edited Jan 5, 2018 at 15:50

Nikos Tavoularis

2,9231 gold badge33 silver badges28 bronze badges

answered Jan 5, 2018 at 15:06

Prathibha

19914 bronze badges

1 Comment

sakirow Over a year ago

when i try to query this the result contains some incorrect informations. {"14":"19","17":"60","18":"67","19":["77","78"],"20":["82","84"],"21":["86"],"23":""} {"14":"","17":"47","18":"74","19":["77"],"20":["82","84"],"23":""} {"14":"","17":"47","18":"74","19":["77"],"20":["82","84"],"23":""} {"14":"","17":"47","18":"70","20":["84"],"23":""} {"14":"21","17":"57","18":"74","19":["78"],"23":""} {"14":"15","17":"51","18":"69","19":["76","78"],"23":""} {"14":"","17":"47","18":"75","19":["80"],"23":""}

Collectives™ on Stack Overflow

SQL regexp query on mysql

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related