Skip to Content

Custom function in BODS to remove special characters from a string

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

9 Comments
You must be Logged on to comment or reply to a post.
  • 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.

  • 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?

    • 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||’ ‘;

  • 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.

    • 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.

  • 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;

     

    • Dear Dilip,

       

      After Creating the Custom function, Where we need to map the Local Variable?

      How to use this Custom function??

      Please correct me…

  •  

    There’s no need for a custom function. This is standard DS functionality. You can use the built-in regex_replace function:

    regex_replace(<input_string>,’\[^a-z0-9\]’,”, ‘CASE_INSENSITIVE’)​