Teradata SQL/procedure : How to replace the multiple occurance of pattern`s in column values

Database
Enthusiast

Teradata SQL/procedure : How to replace the multiple occurance of pattern`s in column values

Hello Together,

In one of our requirement we have to delete the occurance of some patter from column.

Like below example and expected result.

Example:

Col1

Xyz company's are going to announce{ the bonu&#8424s ® for employees.Who completed™

Expected Result

Xyz company's are going to announce the bonu for employees.Who completed

So occurance of those pattern can be more than one below are the pattern can appear many time.

Like

&reg

&#39 to &#8482

&trade

&nsps

How i can achive that in teradata sql using regexp_replace.

Kindly suggest.

I tried with

3 REPLIES
Enthusiast

Re: Teradata SQL/procedure : How to replace the multiple occurance of pattern`s in column values

tried this but it remove all other numbers which appear in string

REGEXP_REPLACE(Col1,'[&#0-9;]','')

Enthusiast

Re: Teradata SQL/procedure : How to replace the multiple occurance of pattern`s in column values

ex

Xyz company's are going to announce{ the bonu&#8424s ® for employees.Who completed™ on 04/27/2016

Result appaer after REGEXP_REPLACE(Col1,'[&#0-9;]','')

like this

Xyz company's are going to announce the bonu for employees.Who completed on / /

I need result like this

Xyz company's are going to announce the bonu for employees.Who completed on 04/27/2016

Senior Apprentice

Re: Teradata SQL/procedure : How to replace the multiple occurance of pattern`s in column values

Looks like your pattern includes everything between & and ;

REGEXP_REPLACE(Col1,'&(.*?);','')