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')​