PHP, MySql & REGEXP – Search string with pattern

Hi, here i would like to share the way to search string with pattern in Mysql. I’m using the Mysql Regexp function.

Example i want to find numbers that match the X pattern in below format:

Format string to search in array:


$format_array = array("12X.LM.XX.XXXX", "123.45.XXXX", "72.67X.XXXX");

foreach ($format_array as $format) {
 $arrExp = explode('.', $format);
 if (preg_match_all("/X+/", $format, $output_array)) {
     foreach ($output_array[0] as $key=>$val) {
         $count = strlen($val);
         foreach ($arrExp as $key=>$s) {
             if (substr_count($s, 'X') == $count) {
                  $arrExp[$key] = str_replace($val, '[a-z0-9.]{'.$count.'}', $s);
     $str_format = implode('.', $arrExp) . "$";
     #example output pattern $str_format = 72.67[a-z0-9.]{1}.[a-z0-9.]{4}$
     #here is your sql command with the pattern
     $sql = "SELECT * FROM table_name WHERE col REGEXP '$str_format";



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s