Oracle remove repeating characters

Question

In order to implement password complexity I need a function that can remove repeating characters (in order to generate passwords that will meet the password complexity requirements). So a string like weeeeee1 will not be allowed since the e is repeated. I need a function that will instead return we1, where the repeating characters have been removed. Oracle PL/SQL or straight sql must be used.

I found Oracle SQL -- remove partial duplicate from string, but this does not work with my weeeeee1 test case, since it only replaces with one iteration, therefore returning weee1.

I'm not trying to test for no repeating characters. I'm trying to change a repeating character string into a non-repeating character string.

Answer 1

I think you can achieve your goal using regexp_replace. Or do I missed something?

select regexp_replace(:password, '(.)\1+','\1') 
  from dual;

---

Here is an example:

with t as (select 'weeee1' password from dual
           union select 'wwwwweeeee11111' from dual
           union select 'we1' from dual)

select regexp_replace(t.password, '(.)\1+','\1') 
  from t;

Producing:

REGEXP_REPLACE(T.PASSWORD,'(.)\1+','\1')
we1
we1
we1

Answer 2

Try this:

select regexp_replace('weeeeee1', '(.)\1+', '\1') val from dual

VAL
---
we1