how to remove multiple semicolons from this data below using pl/sql

Question

I want to remove the multiple semi colons from this query data. i use trim but its not working full.

;[email protected];[email protected];;;[email protected];;[email protected]

Answer 1

There are two ways to do it:

If you know how many semicolons do you want to change: replace function.

select 
replace(';[email protected];[email protected];;;[email protected];;[email protected]',';;',';') S
from dual

SQL Fiddle DEMO

If you want to change two or more semicolons: REGEXP_REPLACE function

select 
REGEXP_REPLACE(';[email protected];[email protected];;;[email protected];;[email protected]','(;){2,}',';') as s
from dual

SQL Fiddle DEMO

More information

• Replace
• REGEXP_REPLACE