REGEX in mysql query

Question

I won't found data from table who start following by CR and one space and 4 to 6 number

Query

SELECT `order_det_ref_no` FROM `manufacturers_order` WHERE
`order_det_ref_no` REGEXP '%^CR\s+\b\w{4,6}$%'

Wiktor Stribiżew · Accepted Answer · 2016-12-09 10:55:56Z

1

MySQL regex does not use regex delimiters, nor does it support \b (it uses [[:<:]] for a word start position and [[:>:]] for a word end), \s, \w regex shorthand classes. Use

REGEXP '^CR[[:space:]]+[[:alnum:]_]{4,6}$'

Note that [[:space:]] matches both horizontal and vertical whitespace, you may use [[:blank:]] if you also need to only match horizontal whitespace.

answered Dec 9, 2016 at 10:55

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

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

4 Comments

user2599329 Over a year ago

Thank you, sir, its working can you give me more detail about REGEXP in mysql query

Wiktor Stribiżew Over a year ago

Yes, see Syntax of MySQL Regular Expressions. The point is that it only supports a sort of POSIX regex flavor that is extremely limited in available features, but is really fast.

user2599329 Over a year ago

Sstr have random value like $str="CR | CM | EL | AD"; how can i add php varibale in regexp REGEXP '^**$str**[[:space:]]+[[:alnum:]_]{4,6}$'

Wiktor Stribiżew Over a year ago

Usually, in PHP, you may concatenate strings with . operator. Grouping in MySQL regex can be done with (....). Also, make sure there are no spaces around |, they will prevent matches. It should look like '^(' . str_replace(' | ', '|', $str) . ')[[:space:]]+[[:alnum:]_]{4,6}$'

Collectives™ on Stack Overflow

REGEX in mysql query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related