From: | rashapoo(at)gmail(dot)com |
---|---|
To: | David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: repeated characters in SQL |
Date: | 2016-01-24 18:05:28 |
Message-ID: | CAFH6ZnKrAXYfNMAu2U=omWFi6bTDbxcTbaXjcmTDr245w7GKZw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Thanks David...so it's looking at each character, storing it in /1, then
comparing the "next" character with what is in /1.
I guess the escape character (which is not needed in, say, Notepad++) threw
me a bit.
On Sun, Jan 24, 2016 at 2:32 AM, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
wrote:
> 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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Francisco Olarte | 2016-01-24 18:31:12 | Re: repeated characters in SQL |
Previous Message | Joshua D. Drake | 2016-01-24 17:50:46 | Re: CoC [Final v2] |