CODEWITHSUNDEEP
sqlstringoracle-databaseregexp-replace
Rafal_PL
Rafal_PL

Reputation: 51

Oracle replace some duplicated characters (non digits )

anyone can help me to build proper syntax for regexp_replace to remove any multiplicated non-digits and non-letters from string ? If digit/letter is multiplicated - it is not changed eg. source and expected result:

'ABBC000001223,  ABC00000212,,, '
'ABBC000001223, ABC00000212, '

(removed second occurance of space after comma and second and third comma )

Upvotes: 1

Views: 41

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21063

Use this REGEXP_REPLACE to match any non alphanumeric character in the first group

 ([^[:alnum:]])

followed by one or more same charcters (group 1)

 ([^[:alnum:]])(\1)+

and replace it with the original character (group 1)

I added some other data to demonstrate the result

with dta as (
select 'ABBC000001223,  ABC00000212,,, ' txt from dual union all
select ',.,;,;;;;,,,,,,,,,,,,#''++`´' txt from dual union all
select 'ABBC000001223ABC00000212' txt from dual)
select txt,
regexp_replace(txt,'([^[:alnum:]])(\1)+', '\1') result
from dta
 

TXT
-------------------------------
RESULT
--------------------------------
ABBC000001223,  ABC00000212,,,  
ABBC000001223, ABC00000212,     
,.,;,;;;;,,,,,,,,,,,,#'++`´     
,.,;,;,#'+`´

ABBC000001223ABC00000212        
ABBC000001223ABC00000212

Upvotes: 2

Related Questions