We can create our own functions by writing script functions in SAP Data Services scripting language using the smart editor. User-script functions return a value.

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.

New Custom Function

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

Custom Function Name

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

Custom Function-Smart Editor

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

New Parameter

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

Custom Function-Parameters

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

Custom Function-Parameters

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.

Custom Function-Local Variable

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

Variable Properties

Custom Function-Variable

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

Custom Function-Variable

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 )
#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;

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

Custom Function-Function Body

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

Custom Function Validate

Next click on OK to save the Custom Function.

Custom Function-Local Object Library

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;

Have a question on this subject?

Ask questions to our expert community members and clear your doubts. Asking question or engaging in technical discussion is both easy and rewarding.

Are you on Twitter?

Start following us. This way we will always keep you updated with what's happening in Data Analytics community. We won't spam you. Promise.