CODEWITHSUNDEEP
mysqlsqlregex
paran
paran

Reputation: 199

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

Upvotes: 1

Views: 86

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

This is not quite an answer, but you can do this with regular expressions. The problem is that MySQL does not support back references. Postgres, does, so the following does what you intend:

SELECT *
FROM numbers
WHERE number ~ '([0-9]{2}).*(\1).*(\1)'

Here is a dbfiddle.

Upvotes: 0

forpas
forpas

Reputation: 164089

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 |

Upvotes: 3

Barmar
Barmar

Reputation: 780984

Part of the problem is that you're not escaping the backslash. Backslash is both a string escape and a regexp escape; to get it into the regexp engine, you need to escape it for the string parser. Otherwise, \2 is treated as simply 2, so ([0-9])\2 matches any digit follwed by 2.

But you don't need to use a back-reference. \2 will match whatever ([0-9]) matched, which will make your code look for XX, not XY. I don't think there's a way to write a regexp where you match any character other than the back-reference.

Upvotes: 1

Related Questions