Replace characters in all the data

[MindaugasBacius]

Hello my dears,

Please help me to replace characters in all the table fields.

Let's say I do have a table (the real table is huge with millions of rows and around 100 columns):

Header1 Header2

AA2A 2DDD

BBB1 EEE1

1CCC FF1F

I need to replace:

1 to 3

2 to 4.

I manage to do it by testing the script:

ReplaceMap:

MAPPING LOAD * INLINE [

char replace

1 3

2 4

] (delimiter is ' ');

TestData:

LOAD

*,

MapSubString('ReplaceMap', Header1) as ReplacedString1;

LOAD

*,

MapSubString('ReplaceMap', Header2) as ReplacedString2;

LOAD * INLINE [

Header1 Header2

AA2A 2DDD

BBB1 EEE1

1CCC FF1F

] (delimiter is ' ');

The result is correct.

But I do wonder if I could optimize the script and remove the loading time. Also, maybe there is a good practise examples of such a task.

Thank you in advance!

[Gysbert_Wassenaar]

Best practice is to fix the problem as close as possible to the source. Maybe you can do this in the source database. Your database administrator should be able to come up with a solution using regular expressions and/or a udf. Or

perhaps you can dump the table to a text file first and use a sed or perl script to replace the characters and then read in the corrected text file.

[tobias_klett]

Hi,

otherwise you would have to use it for each field like this:

DemoTable:

Load replace(replace(Field1,'1','3'),'2','4') as Field1,....

[MindaugasBacius]

I do get your point, Gysbert.

This situation is hardcore and the input files cannot be changed in the way you have suggested.

To be clear, I have found out that the data is in ISO 8859-13 format and should be changed to Windows-1251. I am quite a newbie myself so didn't get it at first sight