CODEWITHSUNDEEP
sqlregexoracle-database
Hawk
Hawk

Reputation: 5170

Delete certain character based on the preceding or succeeding character - ORACLE

I have used REPLACE function in order to delete email addresses from hundreds of records. However, as it is known, the semicolon is the separator, usually between each email address and anther. The problem is, there are a lot of semicolons left randomly.

For example: the field:

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

Let's say that after I deleted two email addresses, the field content became like:

;[email protected];[email protected];

I need to clean these fields from these extra undesired semicolons to be like

[email protected];[email protected]

For double semicolons I have used REPLACE as well by replacing each ;; with ;

Is there anyway to delete any semicolon that is not preceded or following by any character?

Upvotes: 1

Views: 215

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

You remove leading and trailing characters with TRIM:

select trim(both ';' from ';[email protected];;;[email protected];') from dual;

To replace multiple characters with only one occurrence use REGEXP_REPLACE:

select regexp_replace(';[email protected];;;[email protected];', ';+', ';') from dual;

Both methods combined:

select regexp_replace( trim(both ';' from ';[email protected];;;[email protected];'), ';+', ';' ) from dual;

Upvotes: 2

Frank Schmitt
Frank Schmitt

Reputation: 30845

If you only need to replace semicolons at the start or end of the string, using a regular expression with the anchor '^' (beginning of string) / '$' (end of string) should achieve what you want:

with v_data as (
  select '[email protected];[email protected];[email protected];[email protected]' value 
     from dual union all
  select ';[email protected];[email protected];' value from dual
)
select 
  value,
  regexp_replace(regexp_replace(value, '^;', ''), ';$', '') as normalized_value
from v_data

If you also need to replace stray semicolons from the middle of the string, you'll probably need regexes with lookahead/lookbehind.

Upvotes: 2

San
San

Reputation: 4538

regular expression replace can help

select regexp_replace('[email protected];[email protected];;[email protected];;[email protected]', 
                      '[email protected](;)+') as result from dual;

Output:

|                        RESULT |
|-------------------------------|
| [email protected];[email protected] |

Upvotes: 1

Related Questions