SAP Data Services Custom Function

Custom Function

Custom Functions can call other functions but cannot call themselves. Also the functions cannot participate in a cycle of recursive calls. Functions can have parameters for input, output, or both. However, data flows cannot pass parameters of type output or input/output. The return value is predefined to be Return. So lets get started.

Creating string search function - InStr()

Let us create a INSTR custom function which will return the Position of the n-th occurrence of a search string in an input string. First of all go to the Custom Functions tab of the Local Object Library. Next Right-click on the Custom Functions and select New.

Let us enter the Custom Function name as instr. Also we add a description of the custom function along with the input arguments and return datatype as below:

Returns the position within the input 
string where the Nth occurence of the 
search string is contained. The input 
string is scanned from the left, 
starting at the starting position.

instr( [in] InputString As varchar, [in] SearchString As varchar, 
[in] StartingPosition As int, [in] Occurence As int ) As int

Next click on Next and the Smart Editor dialog box for the Custom Function opens.

Next we need to add the Input Arguments or Parameters. So right-click on the Parameters and select Insert.

Enter the Parameter name as $InputString with Data type varchar and Length 10000. The Parameter type is set to Input.

Similiarly create the rest of the Input Parameters types in the proper order namely, $SearchString, $StartingPosition and $Occurence.

By default, the return data type is set to int. In order to modify it, right-click Return and select Properties. Select the desired return data type from the Data type list and click OK.

Next we introduce a few Local Variables in order to fulfill our requirement as a placeholder of intermediate calculations. Right-click on the Local and select Insert.

Enter the Variable name as $LV_Init_Cntr and Data type as int.

Similarly we create two more Local Variables as $LV_Start_Pos and $LV_Pos.

Next we write the program logic in the Smart Editor program area as below:

#Initialize Counter
$LV_Init_Cntr = 0;

#Set Start Position of Search
$LV_Start_Pos = $StartingPosition;

#Loop for Nth Occurence
while ( $LV_Init_Cntr < $Occurence )
begin
		
#Returns Position of SearchString within the InputString starting from the StartPosition
$LV_Pos = index( $InputString, $SearchString, $LV_Start_Pos ); 

#Reset Start Position of Search
$LV_Start_Pos = $LV_Pos + 1;

#Increment Counter
$LV_Init_Cntr = $LV_Init_Cntr + 1;
end

# Return the Position of the Nth Occurence of the SearchString found in the InputString
Return $LV_Pos;

Next Click on the Validate button. No errors were found.

Next click on OK to save the Custom Function.

Next design a sample Job and validate the Custom Function by calling it from a Query transform within a Dataflow or you can use it in a Script task.

Other useful Custom Functions

Function to check for special characters in a string

$Len = length( $InputParameter );
$Check = match_regex( $InputParameter , '[alnum]\{' || $Len || '\}' , 'CASE_INSENSITIVE' );
Return $Check ;

Function to validate ZIP code based on Country codes

if( $ZipCode is null ) return 0;
if( $CountryCode is null ) return 0;

# Validation
if(not $CountryCode in ('DE', 'US', 'CA', 'CN'))
	Return 1;

if( $CountryCode = 'DE' )
	return match_regex($ZipCode, '^[0-9]{5}$', NULL);

if( $CountryCode = 'US' )
	return match_regex($ZipCode, '^[0-9]{5}$|^[0-9]{5}-[0-9]{4}$', NULL);

if( $CountryCode = 'CA')
	return match_regex($ZipCode, '^[A-Z]{1}[0-9]{1}[A-Z]{1} [0-9]{1}[A-Z]{1}[0-9]{1}$', NULL);

if( $CountryCode = 'CN')
	return match_regex($ZipCode, '^[0-9]{6}$', NULL);

return 0;