Hello, I want to remove special characters from the alphanum...

[Devendra Singh 4]

Hello,

I want to remove special characters from the alphanumeric field(e.g. ZIP CODE in our case), as it is giving error while loading data using IWay data migrator(We are using 7705 version).

Special character -

Can anyone help how can we eliminate this before loading the data to target table.

Thanks & Regards,

Devendra

[Debra Waybright]

Have you tried using the REPLACE function:

REPLACE(Input_Field, , );

[john cullen]

Refining Debras suggestion

First you have to find the HEX / DECIMAL value of the invalid character.

See if you can get a copy of the raw data, then display the field in HEX in notepad++

Or, you can make a defined field, converting all the characters to hex.

Once you know your bad character, make a define of the unprintable character.

Here is an example when I had some non-breaking spaces that caused issues.

A non-breaking space is HEX A0, Decimal 160. You cant type it, so you have to define it.

HEX_A0/A1 = HEXBYT(160,A1);

fieldname/A10 = REPLACE(fieldname,HEX_A0, ';

[John Gelona]

If you are on Linux or UNIX you and use the sed command to replace bad characters/strings in the whole file. For example, we import a file from an external agency to update our database. They have Boolean fields in the data. Sometimes, in the same field, they have TRUE or FALSE and sometimes they have 1 or 0. This is a comma delimited file so I use the sed command to change all the ,TRUE, to ,1, and all the ,FALSE, to ,0,. This is much easier than waiting on them to fix their programs.

[Douglas Lee 2]

You may also want to checkout CTRAN