Use of Regex in SQL query

Question

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

What I find strange is that your query is returning all the results that don't match the regex... regex101.com/r/dQrJR7/1 — Niet the Dark Absol
– Niet the Dark Absol, Commented Jul 7, 2019 at 8:15
I don't understand this at all. How does ([0-9])\2 match XY? Shouldn't it match XX? — Barmar
– Barmar, Commented Jul 7, 2019 at 8:17
@Thefourthbird my expected results are only: 1212121205 & 1121212128 — paran
– paran, Commented Jul 7, 2019 at 8:17

forpas · Accepted Answer · 2019-07-07 11:20:34Z

3

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 |

edited Jul 7, 2019 at 11:20

answered Jul 7, 2019 at 8:29

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

paran Over a year ago

If I want XYZ-XYZ, I tried this. Is that a correct way?

forpas Over a year ago

No, because I assume that you want all 3 chars of XYZ to be different or at least not all the same(?). This is more complicated.

paran Over a year ago

Yes all different!

forpas Over a year ago

Instead of left(substring(n.number, s.start, 2), 1) <> right(substring(n.number, s.start, 3), 1) which compares the 1st and the last char of XY you must do 3 comparisons for XYZ, the 1st with the 2nd, the 1st with the 3d, and the 2nd with the 3d must all be different.

paran Over a year ago

I think I am very near to the results: db-fiddle.com/f/2sdEBRtJw8jV9ap7be6FYD/4

|

Barmar · Accepted Answer · 2019-07-07 08:29:10Z

1

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.

answered Jul 7, 2019 at 8:29

Barmar

789k57 gold badges554 silver badges669 bronze badges

1 Comment

paran Over a year ago

I think, this will give me my expected results. Is there any issue in that?

Gordon Linoff · Accepted Answer · 2019-07-07 11:57:03Z

0

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.

answered Jul 7, 2019 at 11:57

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Use of Regex in SQL query

DB-FIDDLE

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related