regex mysql query for specfic string

Question

hello i am trying to search ddw_0_op1 with mysql regex serach query

SELECT * FROM wp_postmeta WHERE meta_key RLIKE '(?=.*ddw_)(?=.*_op1)'

but in results its also displaying results from this field

_ddw_0_op1

but i want to display results only from this field ddw_0_op1 . how i can achieve that in this picture i want results from http links field i hope that makes sense now

Well, .*ddw_ matches _ddw_. Not exactly sure what you're looking for... — ctwheels
– ctwheels, Commented Feb 1, 2018 at 19:58
You need to give better examples of things you do want to match and things that you don't. Do you literally only want to match "ddw_0_op1"? — Patrick Q
– Patrick Q, Commented Feb 1, 2018 at 20:00
Why do you need regex? Try WHERE meta_key LIKE 'ddw_%_op1' or if you need to narrow the wildcard down to digits with regex WHERE meta_key RLIKE '^ddw_[[:digit:]]+_op1$' — bobble bubble
– bobble bubble, Commented Feb 1, 2018 at 20:15

Rick James · Accepted Answer · 2018-02-10 21:43:38Z

1

With INDEX(meta_key), this may be faster:

WHERE meta_key LIKE   'ddw_%_op1'
  AND meta_key RLIKE '^ddw_[[:digit:]]+_op1$'

First the LIKE (without a leading wildcard) can use the index. Then the RLIKE will verify that only digits are in the middle (without using the index).

Since this smells like a WP schema, here are some more tips on wp_postmeta. (The tips won't speed up your ddw query, but may help others.)

edited Feb 10, 2018 at 21:43

answered Feb 10, 2018 at 2:31

Rick James

144k15 gold badges144 silver badges254 bronze badges

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

Collectives™ on Stack Overflow

regex mysql query for specfic string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related