[GENERAL] repeated characters in SQL

[Govind Chettiar]

I have a simple table consisting of a bunch of English words.  I am trying
to find words that have repeated characters in them, for example
apple
tattoo

but not

orange
lemon

I know that only a maximum of one repetition can occur

I tried various options like
SELECT word FROM  public."SpellItWords"
 WHERE word ~ E'(.)\1{2,}'

SELECT word FROM  public."SpellItWords"
 WHERE word ~ E'([a-z])\1{2}'

What finally worked was this
SELECT word FROM  public."SpellItWords"
 WHERE word ~ E'(.)\\1'

But I don't really understand what this does...Can you explain?

Thanks!