need help with postgresql regexp_replace

Question

I need to get rid of trailing repeating non_alphanumeric symbols like

"thanks ! !!!!!!!" to "thanks !"

If these different symbols then they are ignored.

I quite new to regex, so I came up with

regexp_replace('Thanks . . . .  ', '((\\W)\\s*)(\\2\\s*)+', '\\2')

to try it out.

However i realise the trailing space after 'thanks' causes some problem. I would get back "thanks " instead of "thanks ." This is kinda strange because I used an online regex tool and the first whitespace was not matched. Can anyone help?

note: I did insert the double backslash.

You want to get rid of all non-alphanumeric symbols except the last one (the punctuation) or actually get rid of every one of them? Or maybe get rid of repeated punctuation? I'm not sure I understand which one you want. — NorthGuard
– NorthGuard, Commented Jul 22, 2010 at 17:05
actually I want to reduce 2 or more repeating, similar symbols (with or without white space) to just 1. for instance ". ... . ." to just "." stuff like "#%@" will remain the same. — goh
– goh, Commented Jul 22, 2010 at 17:08
How should spaces be dealt with (since it's not alphanumeric)? If you have the string "Thanks !!!! ???" do you want "Thanks !?" or "Thanks ! ?"? Edit: Line break in a bad place, basically if the original string has spaces between repeated different symbols. — NorthGuard
– NorthGuard, Commented Jul 22, 2010 at 17:37

Tim Pietzcker · Accepted Answer · 2010-07-22 19:24:32Z

2

Replace

(\W)(\s*\1)+

with

\1

I don't know PostgreSQL, but from your example, I'm guessing:

regexp_replace('Thanks . . . .  ', '(\\W)(\\s*\\1)+', '\\1')

This will also replace leading multiple spaces with a single space. If you don't want that (i. e. if you want leading spaces to be left alone entirely), then use

([^\s\w])(\s*\1)+   // '([^\\s\\w])(\\s*\\1)+'

instead.

answered Jul 22, 2010 at 19:24

Tim Pietzcker

337k59 gold badges520 silver badges572 bronze badges

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

1 Comment

Alan Moore Over a year ago

That's how I would do it, too (except I would add another \s* to the end to take care of trailing whitespace). But the OP's regex should work--it works fine in RegexBuddy in "Tcl ARE/PostgreSQL" mode.

tinychen · Accepted Answer · 2010-07-23 03:07:04Z

0

try like this:

select regexp_replace('Thanks ! !!!!!!!!', '(\\s*)((\\W)\\s*)(\\2\\s*)+', '\\1\\2');

result:

Thanks !

answered Jul 23, 2010 at 3:07

tinychen

2,0992 gold badges13 silver badges8 bronze badges

Collectives™ on Stack Overflow

need help with postgresql regexp_replace

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related