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.
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’