vendredi 17 juin 2016

MySQL Regular expression for search

I have two tables in MySQL, one of products, and other of exclusions. I have to select all products in table products which are not present in the table exclusions. I have succeeded when the exclusion is made through a "direct" field equivalence, namely, the reference code of the product.

But many products can be excluded through a pattern. It consists of the 3 last digits of the reference code. So, if I have the value "++6", it means that the third digit of that sequence, i.e., the last digit of the reference code, must be different to 6. If it is 6, then the product is excluded. It is here where I am stuck. What regular expression or something should I use? The pattern could perfectly be '0+6', '036', etc.

I paste the structure and data of the example tables, and the SELECT which works with the reference. The problem is to alter the SELECT to include those regular expression (or whatever) that can account for the exclusion with "pattern".

 CREATE TABLE IF NOT EXISTS `exclusions` (
   `id_exclusions` int(11) NOT NULL AUTO_INCREMENT,
   `reference` varchar(10) NOT NULL,
   `pattern` varchar(3) NOT NULL,
   PRIMARY KEY (`id_exclusions`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

 INSERT INTO `exclusions` (`id_exclusions`, `reference`, `pattern`) VALUES
 (1, '8036', ''),
 (2, '', '++5');

  CREATE TABLE IF NOT EXISTS `products` (
   `id_products` int(11) NOT NULL AUTO_INCREMENT,
    `reference` varchar(10) NOT NULL,
   PRIMARY KEY (`id_products`)
 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

 INSERT INTO `products` (`id_products`, `reference`) VALUES
     (1, '8034'),
     (2, '8035'),
      (3, '8036');

Now the SELECT code currently working

SELECT * FROM products AS p LEFT JOIN exclusions AS e ON p.reference = e.reference WHERE e.reference IS NULL

Result:

  id_products, reference, id_exclusions, reference, pattern
  1, 8034, NULL, NULL, NULL
  2, 8035, NULL, NULL, NULL

With the corrected SELECT, it should only return

  id_products, reference, id_exclusions, reference, pattern
  1, 8034, NULL, NULL, NULL

as the 'xx5' reference code is excluded.

Aucun commentaire:

Enregistrer un commentaire