DIGITALREP

A quick look at MySQL regular expressionsOctober 05, 2013

This is a cheat sheet detailing MySQL’s particular flavour of regular expressions. All of the examples use a database table called Shades that contains the fields Red, Green, Blue, Yellow, Purple, Black, and Mother of Pearl.

^ – Beginning
Example: SELECT shade FROM colours WHERE shade REGEXP ‘^bl’
Result: Blue, Black

BINARY – Case Sensitive ON
Example: SELECT shade FROM colours WHERE shade REGEXP BINARY ‘^bl’
Result:

$ – End
Example: SELECT shade FROM colours WHERE shade REGEXP ‘e$’
Result: Blue, Purple

[b-g] – Inclusive Range
Example: SELECT shade FROM colours WHERE shade REGEXP ‘^[b-g]‘
Result: Green, Blue, Black

[^b-g] – NEGATED Inclusive Range
Example: SELECT shade FROM colours WHERE shade REGEXP ‘^[^b-g]‘
Result: Red, Yellow, Purple, Mother of Pearl

. – Any Character (any number)
Example: SELECT shade FROM colours WHERE shade REGEXP ‘.e.’
Result: Red, Green, Yellow, Mother of Pearl

* – Zero or More
Example: SELECT shade FROM colours WHERE shade REGEXP ‘z*’
Result: Red, Green, Blue, Yellow, Purple, Black, Mother of Pearl

+ – One or More
Example: SELECT shade FROM colours WHERE shade REGEXP ‘r+’
Result: Red, Green, Purple, Mother of Pearl

? – Zero or One
Example: SELECT shade FROM colours WHERE shade REGEXP ‘^[^e]*e?[^e]*$’
Result: Red, Blue, Yellow, Purple, Black

| – Or
Example: SELECT shade FROM colours WHERE shade REGEXP ‘ur|b.ack’
Result: Purple, Black

(abc) – Sequence
Example: SELECT shade FROM colours WHERE shade REGEXP ‘(re)’
Result: Red, Green

a{n} – n Instances of a
Example: SELECT shade FROM colours WHERE shade REGEXP ‘l{2}’
Result: Yellow

a{m,n} – Match n-m instances of a
Example: SELECT shade FROM colours WHERE shade REGEXP ‘l{1,2}’
Result: Blue, Yellow, Purple, Black, Mother of Pearl

[[:<:]], [[:>:]] – Word Boundaries
Example: SELECT shade FROM colours WHERE shade REGEXP ‘[[:<:]]of[[:>:]]’
Result: Mother of Pearl

[:character_class:] – Character Class (alpha, digit, space)
Example: SELECT shade FROM colours WHERE shade REGEXP ‘[[:blank:]]’
Result: Mother of Pearl

[.character.] – Character (\n, space, ‘%’, tilde)
Example: SELECT shade FROM colours WHERE shade REGEXP ‘[[.space.]]’
Result: Mother of Pearl

\\ – Literal Interpretation (Do not interpret as regular expression terms)
Example: SELECT shade FROM colours WHERE shade REGEXP ‘\\.+’
Result:

Category: Cheat Sheets
Tags:  

Leave a Reply

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