removing a character using its hexcode

[ashishm]

Hi,

I am writing to XMLs from a flat file, and the source data is rather uncleansed,

We see a 0x1a in the source, which is messing up the output XMl

I tried using an indexof function to find it and remove it, and concatenate the left and the right portions of the string.

Sadly that does not work for this case.

What should I do ?

currently I am using
IF LEN(TRIM(NullToEmpty(ValidCust_In.ADDRESS1))) = 0 THEN
""
ELSE
IF INDEX (StgAddress1, '\0x1a', 1) = 0 THEN
StgAddress1
ELSE
LEFT (StgAddress1, INDEX (StgAddress1, '\0x1a', 1) -1) : "&" : RIGHT(StgAddress1, LEN(StgAddress1) - INDEX(StgAddress1, '\0x1a',1) )

[SURA]

Sv = '\0x1a'
Sv1 = In.col
Sv3 = Sv = Sv1

in the col derivation
If Sv3 Then 0 else col name

DS User

[ray.wurlod]

"0x1a" is 26 in decimal, or Ctrl-Z. Interestingly this is the "end of file" character in (Windows) DOS files. It can be represented in DataStage as Char(26).

Initialize a stage variable, data type Char(1), to Char(26) as its value, then convert to "" in your data:

Code: Select all

Convert(svCtrlZ, "", InLink.TheString)

It might be wise to investigate how this character got into your data in the first place, maybe you can prevent rather than cure.