Remove invalid characters from input data

[#1]

Hi All,

I am using Feddy code to remove invalid character...the code is as below

# CF_SAP_TO_DS_DATA_CLEANSE

#

# Input parameters
# ------------------------
# $PV_FieldName : Primary key
# $PV_FieldName : Field Name
# $PV_FieldVal  : Field Value

#

# Local Variables
# ----------------------
# $LV_FieldVal_Trim  : Trimmed input col val
# $LI_StrLen         : Length of the trimmed input col val
# $LI_Cntr           : Char position identifier of the input col to do iteration
# $LI_Chr            : Current char being checked
# $LI_Chr_Ascii      : Ascii value of current char

# Initialize and define variables
# -----------------------------------------
$LV_FieldVal_Trim   = LTRIM_BLANKS(RTRIM_BLANKS( $PV_FieldVal));
$LI_StrLen          = LENGTH( $LV_FieldVal_Trim );
$PV_FieldName       = LTRIM_BLANKS(RTRIM_BLANKS($PV_FieldName));

$LI_Cntr             = 1;
$LI_Chr              = '';
$LI_Chr_Ascii        = 0;

# Loop through the char set of the input column to check for invalid characters
# -----------------------------------------------------------------------------

while ( $LI_Cntr < $LI_StrLen +1)
begin

       # Iterate through every character of the input field and find its ascii value
       # ---------------------------------------------------------------------------
       $LI_Chr = SUBSTR($LV_FieldVal_Trim,$LI_Cntr,1);
       $LI_Chr_Ascii = ASCII( $LI_Chr );

       # If the char is in valid char list do nothing and check the next char
       # --------------------------------------------------------------------
       if ($LI_Chr_Ascii >= 32 and $LI_Chr_Ascii <=126 )
       begin
              #Do Nothing
       end

       # If the char is not in valid char list cleanse the data

       # --------------------------------------------------------------------------------------

       Else
       begin
              # Replace invalid chars with blanks
              # ---------------------------------
              $LV_FieldVal_Trim = REPLACE_SUBSTR($LV_FieldVal_Trim,$LI_Chr,' ');

       end

       $LI_Cntr = $LI_Cntr + 1;
end

# Return processed field val
# --------------------------
Return $LV_FieldVal_Trim;

ISSUE:

Now I have a postalcode field with the following value: M1S^ ¬ 5N3

When I applied the followwing CF_SAP_TO_DS_DATA_CLEANSE( EDWOH019."0POSTALCODE",1, 10), all the value got removed

POSTALCODE = 'blanks'

Expected result should be POSTALCODE = M1S 5N3

What I am doing wrong? How to apply the custom function?

Thanks,

Blaiso

[venkataramana_paidi · Accepted solution]

Hi Blaiso,

The above code for only ASCII special characters only .

I didn't find any mistake in the above custom function code .  I will try with above custom function .

You can use the below custom function that provided by SAP Data services best quality practices.

$return_str = replace_substr($input_field, '`', '');

$return_str = replace_substr($return_str, '~', '');

$return_str = replace_substr($return_str, '〜', '');

$return_str = replace_substr($return_str, '!', '');

$return_str = replace_substr($return_str, '！', '');

$return_str = replace_substr($return_str, '¡', '');

$return_str = replace_substr($return_str, '@', '');

$return_str = replace_substr($return_str, '#', '');

$return_str = replace_substr($return_str, '$', '');

$return_str = replace_substr($return_str, '¢', '');

$return_str = replace_substr($return_str, '£', '');

$return_str = replace_substr($return_str, '€', '');

$return_str = replace_substr($return_str, '¥', '');

$return_str = replace_substr($return_str, '%', '');

$return_str = replace_substr($return_str, '^', '');

$return_str = replace_substr($return_str, '&', '');

$return_str = replace_substr($return_str, '＆', '');

$return_str = replace_substr($return_str, '*', '');

$return_str = replace_substr($return_str, '＊', '');

$return_str = replace_substr($return_str, '(', '');

$return_str = replace_substr($return_str, '（', '');

$return_str = replace_substr($return_str, ')', '');

$return_str = replace_substr($return_str, '）', '');

$return_str = replace_substr($return_str, '-', '');

$return_str = replace_substr($return_str, '－', '');

$return_str = replace_substr($return_str, '_', '');

$return_str = replace_substr($return_str, '=', '');

$return_str = replace_substr($return_str, '+', '');

$return_str = replace_substr($return_str, '/', '');

$return_str = replace_substr($return_str, '／', '');

$return_str = replace_substr($return_str, '\\', '');

$return_str = replace_substr($return_str, '|', '');

$return_str = replace_substr($return_str, '?', '');

$return_str = replace_substr($return_str, '？', '');

$return_str = replace_substr($return_str, '¿', '');

$return_str = replace_substr($return_str, '\\[', '');

$return_str = replace_substr($return_str, '［', '');

$return_str = replace_substr($return_str, ']', '');

$return_str = replace_substr($return_str, '］', '');

$return_str = replace_substr($return_str, '\\{', '');

$return_str = replace_substr($return_str, '｛', '');

$return_str = replace_substr($return_str, '}', '');

$return_str = replace_substr($return_str, '｝', '');

$return_str = replace_substr($return_str, '<', '');

$return_str = replace_substr($return_str, '〈', '');

$return_str = replace_substr($return_str, '>', '');

$return_str = replace_substr($return_str, '〉', '');

$return_str = replace_substr($return_str, '《', '');

$return_str = replace_substr($return_str, '》', '');

$return_str = replace_substr($return_str, ',', '');

$return_str = replace_substr($return_str, '，', '');

$return_str = replace_substr($return_str, '、', '');

$return_str = replace_substr($return_str, '.', '');

$return_str = replace_substr($return_str, '．', '');

$return_str = replace_substr($return_str, ';', '');

$return_str = replace_substr($return_str, '；', '');

$return_str = replace_substr($return_str, ':', '');

$return_str = replace_substr($return_str, '：', '');

$return_str = replace_substr($return_str, '\'', '');

$return_str = replace_substr($return_str, '’', '');

$return_str = replace_substr($return_str, '"', '');

$return_str = replace_substr($return_str, '〃', '');

$return_str = replace_substr($return_str, '「', '');

$return_str = replace_substr($return_str, '」', '');

$return_str = replace_substr($return_str, '『', '');

$return_str = replace_substr($return_str, '』', '');

$return_str = replace_substr($return_str, '﹁', '');

$return_str = replace_substr($return_str, '﹂', '');

$return_str = replace_substr($return_str, '【', '');

$return_str = replace_substr($return_str, '】', '');

$return_str = replace_substr($return_str, '…', '');

$return_str = replace_substr($return_str, '･', '');

$return_str = replace_substr($return_str, '・', '');

$return_str = replace_substr($return_str, '〔', '');

$return_str = replace_substr($return_str, '〕', '');

$return_str = replace_substr($return_str, '〘', '');

$return_str = replace_substr($return_str, '〙', '');

$return_str = replace_substr($return_str, '〚', '');

$return_str = replace_substr($return_str, '〛', '');

$return_str = replace_substr($return_str, '。', '');

$return_str = replace_substr($return_str, '¬', '');

$return_str = replace_substr(replace_substr(rtrim_blanks(ltrim_blanks($return_str)), '  ', ' '), '  ', ' ');

RETURN $return_str;

[former_member649905 · Accepted solution]

Hi Venkata,

Thanks for your input...anyway I reviewed my code and I was able to find out why it was not working as expected. I fixed it and now it is working as expected. Issue it's that I forgot to defined return parameter as Varchar in the case.

Thanks,

Blaiso