Get row from mysql using specific value with regexp ( json string )

Question

I'm storing permissions into DB with Array JSON String, and i want select them by permission specific permission. at this time I'm selecting them like this:

  1 | Dog   | [3,4]
  2 | Cat   | [33,4]
  3 | Tiger | [5,33,4]
  4 | wolf  | [3,5]


 SELECT * FROM `pages` WHERE access REGEXP '([^"])3([^"])'

it works but not as it should work. This query gives me all records which contains 3 but also it gives which contains 33. my question is how i must format my regexp to get row by specific value into json string.

p.s i have mysql 5.5 so as i know on this version json functions is not supported

Possible duplicate of check if a value exists in json encode array in mysql — e4c5
– e4c5, Commented Jan 4, 2017 at 15:33
Like the duplicate says you shouldn't be using JSON if oyu are on mysql 5.5 you are going to have all sorts of problems with this. Either upgrade or ditch this design. Read up on 'database normalization' — e4c5
– e4c5, Commented Jan 5, 2017 at 1:01

CCH · Accepted Answer · 2017-01-04 15:44:42Z

1

If you only have numbers in the fields, you can alter your regexp to only take values where the string you are looking for (here the '3') does not have another number immediately close to it :

SELECT * FROM `pages` WHERE access REGEXP '([^"0-9])3([^"0-9])'

answered Jan 4, 2017 at 15:44

CCH

1,5771 gold badge13 silver badges24 bronze badges

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

Comments

Rick James · Accepted Answer · 2017-01-04 18:56:22Z

0

REGEXP '[[:<:]]3[[:>:]]'

That is, use the "word boundary" thingies.

answered Jan 4, 2017 at 18:56

Rick James

144k15 gold badges144 silver badges254 bronze badges

Collectives™ on Stack Overflow

Get row from mysql using specific value with regexp ( json string )

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related