sql – MYSQL REGEXP operation

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

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

Tags