How do I use a regex in my MySQL query?

Question

I want to search for a word+number if that SQL returns nothing, I will default it to a full LIKE search, is there any way to implement regex like this in SQL?

Example:

$queryWord = potato

It should first search for potato+somenumber up to 4 digits
It should return all matches with potato+somenumber
If that fails, it defaults to a full search with wildcards on both sides

Like this:

$sql = "SELECT * FROM `words` WHERE `searchWord` LIKE '$queryWord+number';";
$result = mysqli_query($con, $sql);

if (mysqli_num_rows($result) == 0) {

    $sql =  "SELECT * FROM `words` WHERE `searchWord` LIKE '%$queryWord%';"; // full search
    $result = mysqli_query($con, $sql);
    // etc etc

}

Mike Perrenoud · Accepted Answer · 2013-05-16 19:45:52Z

3

Just change your query to this:

$sql = "SELECT * FROM `words` WHERE `searchWord` REGEXP '$queryWord(\d{1,4})';";

and here is a Rubular that proves the Regex.

answered May 16, 2013 at 19:45

Mike Perrenoud

68.1k32 gold badges167 silver badges238 bronze badges

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

1 Comment

Henrique Barcelos Over a year ago

Just to say, there are 2 keywords that can be used in MySQL for regular expression matching: REGEXP and RLIKE, they are the same.

Collectives™ on Stack Overflow

How do I use a regex in my MySQL query?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related