Skip to Content

Below is step by step procedure to write a custom function in BODS to remove special characters in a string using ASCII values.

Step 1: Create a custom function in BODS and name it as ‘CF_REMOVE_SPECIAL_CHARS’

Step 2: Use the below code in your function.

# This function is to remove special characters from the string.It only retains alphabets and numbers from the string.

$L_String =$P_Input_Field;

$L_String_Length =length( $L_String );

$L_Counter =1;

$L_String_final =null;

while($L_String_Length>0)

begin

$L_Char =substr( $L_String ,$L_Counter,1);

if((ascii($L_Char)>=48 and ascii($L_Char)<=57) or (ascii($L_Char)>=65 and ascii($L_Char)<=90) or (ascii($L_Char)>=97 and ascii($L_Char)<=122))

begin

$L_String_final =$L_String_final||$L_Char;

$L_Counter =$L_Counter+1;

$L_String_Length =$L_String_Length-1;

end

else

begin

$L_Counter =$L_Counter+1;

$L_String_Length = $L_String_Length-1;

end

end

Return replace_substr( replace_substr( rtrim_blanks( rtrim_blanks( $L_String_final )),’  ‘,’ ‘),’  ‘, ‘ ‘);

Your code in Editor would look like as under:

/wp-content/uploads/2014/03/fun_417642.png

Step 3: Declare Parameters and local variables as shown in left pane of the above function editor.

$P_Input_Field – parameter type is input (data type varchar(255) )

$L_Char – datatype varchar(255)
$L_Counter – datatype int
$L_String – datatype varchar(255)
$L_String_final – datatype varchar(255)
$L_String_Length – datatype int

Note: Change the parameter return type to Varchar(255). By default return type is int.

Step 4: Save this function.

Step 5: Call this function while mapping any field in Query Editor where you want to remove special characters.

Ex: CF_REMOVE_SPECIAL_CHARS(Table1.INPUT_VAL)

Above function call shall remove all special characters from INPUT_VAL field in a table1 and output Value shall look like below table’s data.

TBL.png

To report this post you need to login first.

7 Comments

You must be Logged on to comment or reply to a post.

  1. subbu cherukuri

    Hi Mohammad Shahanshah Ansari,

    Thanks for the script provided above i have used it in my project and it is working fine  and my requirement is like i have to remove the characters(A-Z) alphabets as well from the string,

    One more requirement if there is alphabets in the string i have to filter it. kindly provide me the solution how to achieve this

    Thanks in advance,

    Subbu.

    (0) 
  2. Ratu Kai

    Thanks for sharing this…Do you know how to make this work so that the special characters are not only removed, BUT also replaced with a space?

    (0) 
    1. Joshua Madden

      I am fairly sure, based on what I see that you can add this line after the second ‘begin’ and it will do that for you:

      $L_String_final =$L_String_final||’ ‘;

      (0) 
  3. Praveen Kumar

    Hi Mohammad,

    Very good Doc.

    I need one clarification, we have the data in the source file and in this case how to assign the source field column data to the $Parameter value.

    Please help.

    Regards,

    Praveen.

    (0) 
    1. Néstor Martínez

      Hey Praveen,

      Into a transformation object, you must to create a new function call in the output side, passing as input parameter the field you want to parse from the source file.

      (0) 
  4. dilip kumar

    We can get same output with the help of simple custom function

     
    $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(replace_substr(rtrim_blanks(ltrim_blanks($return_str)), ‘  ‘, ‘ ‘), ‘  ‘, ‘ ‘);
    RETURN $return_str;

     

    (0) 

Leave a Reply