mysql - SQL 查询中正则表达式的使用

Question

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

试图从数据库中仅选择 XY-XY-XY 系列:

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

给我结果:

1212121205, 1121212128 & 1234123425

---

XY-XY-XY 系列如何1234123425？

DB-FIDDLE

Answer 1

你所有的问题都是有趣的sql谜题。
此解决方案也不涉及正则表达式:

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), '%'
  )

参见 demo .
结果:

| number     |
| ---------- |
| 1212121205 |
| 1121212128 |