Skip to Content
Author's profile photo Mohammad Shahanshah Ansari

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

Assigned tags

      9 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      Former Member

      Hi,

      very good information. thanks for sharing.

      Regards,

      Prasanna

      9738221174

      Author's profile photo Former Member
      Former Member

      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?

      Author's profile photo Former Member
      Former Member

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

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      Former Member

      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;

       

      Author's profile photo Former Member
      Former Member

      Dear Dilip,

       

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

      How to use this Custom function??

      Please correct me...

      Author's profile photo Dirk Venken
      Dirk Venken

       

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