Skip to content
Advertisement

Use of Regex in SQL query

create table numbers (number varchar(10));
insert into numbers (number) values
('9999999999'),
('5532003644'), 
('1212121205'),                                      
('1103220311'),                                     
('1121212128'),
('1234123425');                                    

Trying to SELECT only XY-XY-XY series from the database:

SELECT * FROM numbers
where number regexp '(.*([0-9])2.*){3}'

Giving me results:

1212121205, 1121212128 & 1234123425


How 1234123425 is XY-XY-XY series?

DB-FIDDLE

Advertisement

Answer

All your questions are interesting sql puzzles.
This solution also, does not involve regex:

select distinct n.number
from (
  select 1 start union all select 2  union all select 3 union all 
  select 4 union all select 5 
) s cross join numbers n
where 
  left(substring(n.number, s.start, 2), 1) <> right(substring(n.number, s.start, 2), 1)
  and
  n.number like concat(
    '%', substring(n.number, s.start, 2), 
    substring(n.number, s.start, 2),
    '%', substring(n.number, s.start, 2), '%'
  )

See the demo.
Results:

| number     |
| ---------- |
| 1212121205 |
| 1121212128 |
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement