On 24 January 2016 at 12:44, Govind Chettiar <rashapoo@xxxxxxxxx> wrote: > 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? The ~ operator is a regular expression matching operator, and the (.)\1 is a regular expression. More details here http://www.postgresql.org/docs/current/static/functions-matching.html The regular expression . matches a single character, since that . is wrapped in () the regex engine captures the match and stores it in a variable, this is called a capture group. Since this is the first such capture group in the regular expression, then the value matching the . gets stored in the variable \1, so your regex basically says; "match a single character which has the same single character to its immediate right hand side". The extra \ is just an escape character. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general