Question 250228 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How can I find records using regex

I have some products in my table and each product has a unique number. Few examples are given below:

H-E223I22A1234  
RW-E123I22B2345  
E223I22A4545

Above codes are following below format:

(H|RW)- = Optional  
E1|E2... = This will be any series of only 2 characters (Preferably we will follow aplhabet and number)  
23 = This is current last 2 digit of current year  
I = Alphabet representation of current month. In this I = September(09)  
22 = Current date. it will be 01-30|31
A|B = It is Side of production. Will always be either A or B  
1234 = 4 digit random number

Now I want to find out all products by series and current month and year. For example, If I want to find product of E2 series of September 2023 then what will be the MySQL query. I was trying to achieve the same with regular expression, but MySQL always return all records. Below is my regular expression:

Select * from products where code REGEXP '((H|RW)-)?(([A-Z]{1}[1-9]{1}){1})(23)(I)(0?[1-9]|[12][0-9]|3[01])[A|B](d*)'
Tags:

2

Answers


  1. 0

    You can build your regex from the component parts you are interested in. For example, using variables:

    SET @month = 9;
SET @year = 2023;
SET @day = 22;
SET @product = 'E2';
SET @regex = CONCAT('((H|RW)-)?', @product, RIGHT(@year, 2), SUBSTRING('ABCDEFGHIJKL', @month, 1), @day, '[AB]\d{4}');

    This results in

    ((H|RW)-)?E223I22[AB]d{4}

    You can then get results by:

    SELECT *
FROM test
WHERE code REGEXP @regex

    Output for your sample data:

    code
H-E223I22A1234
E223I22A4545

    Demo on dbfiddle.uk

    You may want to add start (^) and end-of-string ($) anchors to the regex if necessary.

    Login or Signup to reply.
  2. 0

    You can use this pattern for matching.

    (?:H-|RW-)?[A-Z]d23[A-L]dd(?:A|B)d{4}

    And, this pattern for capturing.

    (H-|RW-)?([A-Z]d)23([A-L])(dd)(A|B)(d{4})
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search