MYSQL REGEXP operation

Question

I saw this part of MySQL query code in the Project I handle. The code was coded 5 years ago.

CAST((CASE
    WHEN cat.title REGEXP "^[0-9][0-9][0-9][0-9]" THEN LEFT(cat.title,4)
    WHEN cat.title REGEXP "^[0-9][0-9][0-9]" THEN LEFT(cat.title,3)
    WHEN cat.title REGEXP "^[0-9][0-9]" THEN LEFT(cat.title,2)
    WHEN cat.title REGEXP "^[0-9]" THEN LEFT(cat.title,1)
ELSE NULL END) AS SIGNED) int_cat,
IF((CASE
    WHEN cat.title REGEXP "^[0-9][0-9][0-9][0-9]" THEN LEFT(cat.title,4)
    WHEN cat.title REGEXP "^[0-9][0-9][0-9]" THEN LEFT(cat.title,3)
    WHEN cat.title REGEXP "^[0-9][0-9]" THEN LEFT(cat.title,2)
    WHEN cat.title REGEXP "^[0-9]" THEN LEFT(cat.title,1)
ELSE NULL END) IS NULL, cat.title, NULL) int_cat_string,

can someone explain how this query working? what is the usage of REGEXP and THEN LEFT I want to refactor this code. I just a hard time thinking this area of QUERY.

Answer 1

In simple words it searches for the first positions and expect to be numbers between 0-9.

"^[0-9][0-9][0-9][0-9]" ---> first 4 positions

"^[0-9][0-9][0-9]" ---> first 3 positions

"^[0-9][0-9]" ---> first 2 positions

"^[0-9]" ---> first position

Follow below example:

create table cat (
title varchar(20));

insert into cat values ('ab2cd978'),('1bg54g78g'),('102578'),('aa45'),('123'); 

select title,case WHEN cat.title REGEXP "^[0-9][0-9][0-9][0-9]" THEN LEFT(cat.title,4) end as nr4,
             case  WHEN cat.title REGEXP "^[0-9][0-9][0-9]" THEN LEFT(cat.title,3) end as nr3 ,
             case WHEN cat.title REGEXP "^[0-9][0-9]" THEN LEFT(cat.title,2) end as nr2,
             case WHEN cat.title REGEXP "^[0-9]" THEN LEFT(cat.title,1) end as nr1
 from cat  

Result:

title       nr4    nr3    nr2   nr1
ab2cd978    null   null   null  null              
1bg54g78g   null   null   null   1
102578      1025   102    10     1
aa45        null   null   null  null      
123         null   123    12     1

The results for the value ab2cd978 are all null because any of the condition isn't true, any of the 4 first positions isn't a number between 0-9

https://www.mysqltutorial.org/mysql-regular-expression-regexp.aspx