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 |