Replace all characters in string except particular words

Aster

Replace all characters in string except particular words

Dear All,

I am trying to replace all characters in string except particular words using regexp_replace() function in Aster.

For example, replace all chars in string "I will go to Jakarta, Bandung and Bali next month" with "X", except "Jakarta", "Bandung" and "Bali". So, expected result will be "X JakartaX Bandung X Bali X".

I know how to replace all chars except particular chars using regex_replace. Ex: regexp_replace('baca buku','[^abc]','X','g') will return "bacaXbXXX".

Any suggestions are really appreciated ..

3 REPLIES
Teradata Employee

Re: Replace all characters in string except particular words

I have not used database regex_replace, but using any regex function I think you could embded a series to incrementtally replace.  

1. Replace Jakarta with a single char - say "+"

2. Replace all alpha with X

3. Replace "+" with Jakarta

Perhaps something like this?

regexp_replace(regexp_replace( regexp_replace(string_col, 'Jakarta', '+'), '[A-Z]{0,}' 'X'), '+', 'Jakarta')

Re: Replace all characters in string except particular words

Hi,

I am getting errors while loading data from text file using ncluster_loader .The text file have null values in it.

I tried the option --preprocess-script=removenulls.sh,but its not working.

example:

 ncluster_loader --hostname 10.10.10.1 --username beehive --password beehive --dbname beehive --delimiter ',' --verbose  --preprocess-script=removenulls.sh tab1 ipfile.txt

Kindly let me know the solution to load text files with null values to table

Enthusiast

Re: Replace all characters in string except particular words

If you have a null character representer , try using option "-n".   

ncluster_loader --hostname 10.10.10.1 --username beehive --password beehive --dbname beehive -n "null character representer" --delimiter ',' --verbose  --preprocess-script=removenulls.sh tab1 ipfile.txt