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
Read more here: Source link