Re: repeated characters in SQL

On 24 January 2016 at 12:44, Govind Chettiar <rashapoo(at)gmail(dot)com> 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