regex_replace doesn't replace the hyphen/dash

Question

I'm using regex_replace in postgreSQL and trying to strip out any character in a string that is not a letter or number. However, using this regex: ``` select * from regexp_replace('blink-182', '[^a-zA-Z0-9]*$', '') ``` returns 'blink-182'. The hyphen is not being removed and replaced with nothing ('') as I would expect. How do I modify this regex to also replace the hypen - I've tested with many other characters (!,.#) and they are all replaced correctly. Any ideas?

Accepted Answer

You currently replace a run of non-alphanumeric characters at the end of the string only. I guess your tests were mainly strings of the form `foobar!#` which worked because the characters to remove were at the end of the string.

To replace every occurrence of such a character in the string remove the `$` from the regex:

```
[^a-zA-Z0-9]+
```

(also I changed the `*` into a `+` to prevent zero-length replaces between every character.

If you want to retain whitespace as well you need to add it to the character class:

```
[^a-zA-Z0-9 ]+
```

or possibly

```
[^a-zA-Z0-9\s]+
```

If the regex in the beginning was in fact correct in that you only want to remove non-alphanumeric characters from the end of the string but you also want to remove hyphen-minus in the middle of a string (while retaining other non-alphanumeric characters in the middle of the string), then the following should work:

```
[^a-zA-Z0-9]+$|-
```

maniek points out that you need to add an argument to `regexp_replace` so it will replace more than once match:

```
regexp_replace('blink-182', '[^a-zA-Z0-9]+$|-', '', 'g')
```

regex_replace doesn't replace the hyphen/dash

Solution

Problem