March 25, 2014 1 minute read

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

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

Your code in Editor would look like as under:

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.

10 Comments

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

Former Member

December 12, 2014 at 12:42 pm

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.

Former Member

March 19, 2015 at 5:51 am

Hi,

very good information. thanks for sharing.

Regards,

Prasanna

9738221174

Former Member

June 18, 2015 at 8:26 pm

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?

Former Member

February 11, 2016 at 1:09 pm

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

Former Member

November 11, 2015 at 6:41 pm

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.

Former Member

November 12, 2015 at 12:06 pm

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.

Former Member

March 9, 2017 at 5:12 pm

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;

Former Member

March 17, 2018 at 2:36 pm

Dear Dilip,

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

How to use this Custom function??

Please correct me...

Dirk Venken

March 21, 2018 at 6:54 am

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

Elizabeth T

February 10, 2022 at 7:18 am

Hi Dirk,

Can we use the same function to replace repeating characters in a string?

I tried this but this is not working. Tried with CASESENSITIVE also.

regex_replace(LFB1.ZWELS,'\[A-Z0-9\]\1+','\1', 'CASE_INSENSITIVE')

I derived this from HANA SQL which is working as expected

select REPLACE_REGEXPR ('([A-Za-z0-9])\1+' in 'AA2234b23'
WITH '\1'
OCCURRENCE ALL)

Could you help with the correct syntax if it works or suggest a method on how I can achieve this is BODS.

Thanks

Elizabeth T

Custom function in BODS to remove special characters from a string

Assigned Tags