CODEWITHSUNDEEP
sqlmysqldatabaseregex
Ed Taylor
Ed Taylor

Reputation: 277

How to write MySQL REGEXP?

A table contains the string "Hello world!"

Thinking of * as the ordinary wildcard character, how can I write a REGEXP that will evalute to true for 'W*rld!' but false for 'H*rld!' since H is part of another word. 'W*rld' should evalute to false as well because of the trailing '!'

Upvotes: 0

Views: 534

Answers (3)

Doug Neiner
Doug Neiner

Reputation: 66191

If you are just looking to match the word world, then do this:

SELECT * FROM `table` WHERE `field_name` LIKE "w_rld!";

The _ allows for a single wildcard character.

Edit: I realize the OP requested this solution with REGEXP, but since the same result can be achieved without using regular expressions, I provided this as viable solution that should perform faster than a REGEXP.

Upvotes: 2

OMG Ponies
OMG Ponies

Reputation: 332531

Use:

WHERE column REGEXP 'W[[:alnum:]]+rld!'

Alternately, you can use:

WHERE column RLIKE 'W[[:alnum:]]+rld!'
  • RLIKE is a synonym for REGEXP
  • [[:alnum:]] will allow any alphanumeric character, [[:alnum:]]+ will allow multiples
  • REGEXP \ RLIKE is not case sensitive, except when used with binary strings.

Reference: MySQL Regex Support

Upvotes: 8

Mark Byers
Mark Byers

Reputation: 838076

You can use regular expressions in MySQL:

SELECT 'Hello world!' REGEXP 'H[[:alnum:]]+rld!'
0
SELECT 'Hello world!' REGEXP 'w[[:alnum:]]+rld!'
1

More information about the syntax can be found here.

Upvotes: 1

Related Questions