Re: Regular Expression For Duplicate Words - Mailing list pgsql-general

From Peter J. Holzer
Subject Re: Regular Expression For Duplicate Words
Date
Msg-id 20220203194800.pg3bzm33dzzdpqfb@hjp.at
Whole thread Raw
In response to Regular Expression For Duplicate Words  (Shaozhong SHI <shishaozhong@gmail.com>)
Responses Re: Regular Expression For Duplicate Words
List pgsql-general
On 2022-02-02 08:00:00 +0000, Shaozhong SHI wrote:
> regex - Regular Expression For Duplicate Words - Stack Overflow
>
> Is there any example in Postgres?

It's pretty much the same as with other regexp dialects: User word
boundaries and a word character class to match any word and then use a
backreference to match a duplicate word. All the building blocks are
described on
https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-REGEXP
and except for [[:<:]] and [[:>:]] for the word boundaries, they are
also pretty standard.

So

[[:<:]]        start of word
([[:alpha:]]+) one or more alphabetic characters in a capturing group
[[:>:]]        end of word
\W+            one or more non-word characters
[[:<:]]        start of word
\1             the content of the first (and only) capturing group
[[:>:]]        end of word

All together:

select * from t where t ~ '[[:<:]]([[:alpha:]]+)[[:>:]]\W[[:<:]]\1[[:>:]]';

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Attachment

pgsql-general by date:

Previous
From: A Shaposhnikov
Date:
Subject: Re: increasing effective_cache_size slows down join queries by a factor of 4000x
Next
From: Vijaykumar Jain
Date:
Subject: Re: increasing effective_cache_size slows down join queries by a factor of 4000x