PostgreSQL - query all multidigit numbers with repeating digits identical (repdigit, "Schnapszahl")

Question

I only want to have the entries with the marked numbers

1234

11983455

6526347

11

207555

777

343333987

34678

Answer 1

This regular expression seems to do it:

select nr
from numbers
where nr::text ~ '([0-9])(\1)';

([0-9]) creates a group for a single digit. (\1) references the first group in the regex. So ([0-9])(\1) means: "a digit followed by the same value"

Output for your sample data is:

with numbers (nr) as (
  values 
    (1234),(11983455),(6526347),(11),(207555),(777),(343333987),(34678)
)
select nr
from numbers
where nr::text ~ '([0-9])(\1)';

nr       
---------
 11983455
       11
   207555
      777
343333987

---

^{Although I only consider 11 and 777 to be a "Schnapszahl"}