Remove Consecutive Repeated Characters with PostgreSQL regexp_replace function

Question

Remove all consecutive repeated characters using Regular Expression.

In Javascript this works well:

txt='aaa bbb 888 bbb ccc ddd'.replace(/(?!(?!(.)\1))./g,'');

Returns 'a b 8 b c d'

How can I do it with Posgresql regexp_replace function? This won't work:

SELECT regexp_replace('aaa bbb 888 bbb ccc ddd',E'(?!(?!(.)\\\\1)).','g');

$ psql -c "SELECT regexp_replace('aaa bbb 888 bbb ccc ddd',E'(?!(?!(.)\\1)).','g');"
     regexp_replace      
-------------------------
 aaa bbb 888 bbb ccc ddd
(1 row)

$ psql -c "SELECT regexp_replace('aaa bbb 888 bbb ccc ddd','(?!(?!(.)\1)).','g');"   
ERROR:  invalid regular expression: invalid backreference number

What am I doing wrong?

select string_agg(x[1],'') from regexp_matches('aaa bbb 888 111122222 1234 bbb ccc ddd','(.)\1*','g') t(x); — Abelisto
– Abelisto, Commented Aug 16, 2016 at 7:46
@Abelisto This returns a b 8 12 1234 b c d, which isn't exactly what the OP wanted, but I upvoted your comment because it is close. — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 16, 2016 at 7:49
@TimBiegeleisen It is because different input to test some more use cases. — Abelisto
– Abelisto, Commented Aug 16, 2016 at 7:52
@TimBiegeleisen To get the first character of every word (not just delete repeated characters): select string_agg(x,'') from unnest(array(select regexp_matches('aaa bbb 888 111122222 1234, bbb ccc ddd','\m(\w)\w*(\W*)','g'))) t(x); — Abelisto
– Abelisto, Commented Aug 16, 2016 at 8:23
@Abelisto - You should put this down as an answer. Your first comment perfectly solves the problem. — Addison
– Addison, Commented Nov 28, 2016 at 5:35

Community · Accepted Answer · 2017-05-23 10:29:16Z

6

There's a similar SO question that can help you to get the answer:

SELECT regexp_replace('aaa bbb 888 bbb ccc ddd', '(.)\1{1,}', '\1', 'g');
 regexp_replace 
----------------
 a b 8 b c d
(1 row)

It uses a backreference to capture the groups of repeating characters.

edited May 23, 2017 at 10:29

CommunityBot

11 silver badge

answered Jan 22, 2017 at 0:38

mgamba

1,2099 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Remove Consecutive Repeated Characters with PostgreSQL regexp_replace function

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related