Skip to Content
Author's profile photo Ambily Varghese

Custom function to make a string only numeric

If you have a scenario as given below:
A string is  alphanumeric and output should be only numeric

For example

Input data
N055a615
89712aX7hY
455,651%

Desired output
055615
897127
455651

Custom function used to achieve this is given below

Parameters used are :

$P_Input is the input parameter to which the input values are given.
Return parameter’s datatype is changed to varchar(255) from default int.

Variables used and its datatype are:

$L_Char       varchar(255)
$L_Count     int
$L_String     varchar(255)
$L_String_Length     int
$L_String_Final        varchar(255)

Function is 

$L_String=$P_Input;
$L_String_Length=length( $L_String);
$L_Count =1;
$L_String_Final = null;

while($L_String_Length>0)
begin
$L_Char =substr( $L_String,$L_Count,1);

if (ascii( $L_Char)>=48 and ascii( $L_Char)<=57)

begin
$L_String_Final=$L_String_Final||$L_Char;

$L_Count=$L_Count+1;

$L_String_Length=$L_String_Length-1;
end
else

begin
$L_Count=$L_Count+1;
$L_String_Length=$L_String_Length-1;
end

end

Return $L_String_Final;

Function uses ascii codes for the numbers 0-9.
Each character of the string is taken and checked with the ascii code condition.if the character is numeric then it is concatenated to a variable else it is ignored and move to next character.Loop runs until the end of the string.

Same function can be modified to get only letters or a desired combination like(alphabets+special characters or numbers with space or special characters) by changing the ascii code values in the condition.

This function is relevant and useful till version up to DS4.2 SP4 and we can use regex_replace in recent versions. This function comes under string functions.

For example in to get only numeric you can use
regex_replace( ‘455,651%’, ‘\D’, ”) returns ‘455651
\D is the short hand for [0-9]

PS: I have tried using regex_replace function in a job and got the below error as it is not there in
DS 4.2 older versions.So if you are facing such errors then you can try this custom function.

Assigned Tags

      1 Comment
      You must be Logged on to comment or reply to a post.
      Author's profile photo Dirk Venken
      Dirk Venken

      Don’t reinvent the wheel, if not necessary. There is not always a need for a custom function!
      Starting from DS 4.2 SP3, this functionality can easily be achieved by a new standard built-in function called regex_replace.
      E.g: regex_replace( ‘N055a615’, ‘\D’, ”) returns ‘055615’