MySql

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:

<?php

$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);
                  break;
             }
         }
     }
 
     $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";
  }
}
?>

PROCEDURE abc(pmId INT, dateFrom DATE, dateTo DATE)
BEGIN
        DECLARE str TEXT;
        DECLARE dff DATETIME;
        DECLARE dnn DATETIME;
        DECLARE dn DATE;
    DECLARE df DATE;

SET df = dateFrom;

    WHILE DATE(df) <= DATE(dateTo) DO
            SET dn = DATE_ADD(df, INTERVAL 1 DAY);

            SET dff = CONCAT(df, ‘ 08:00:00’);
            SET dnn = CONCAT(dn, ‘ 07:59:00’);

            SELECT ‘CT1′ as trip, DATE_FORMAT(date,’%d-%m-%Y’) as date,GROUP_CONCAT(DISTINCT CAST(driver_id AS CHAR)) as
                        dri, count( IF(size=20, 1, NULL ) ) AS ctOne20, count( IF(size=40, 1, NULL ) ) AS ctOne40
            FROM trip_ct WHERE pm_id=pmId AND DATE_ADD(date, INTERVAL time HOUR_SECOND) BETWEEN dff AND dnn;

            SET df = DATE_ADD(df, INTERVAL 1 DAY);

    END WHILE;
END

PHP, MySql & REGEXP – Search string with pattern

Mysql:: Stored Procedure:: Looping Daily Date With Time Range