on 04-15-2014 4:59 PM
Hi
I am using BODS 14.1.1.210
I would want to extract just numbers from the alphanumeric column as shown below. How can I achieve this?
INPUT OUTPUT
ABC_12 12
A@20MN 20
BMX>56 56
34MNB 34
Cheers!
Hi Josh,
Create the below Custom Function in BODS and use it. It shall give you expected result.
Step 1: Create a custom function and name it as CF_Extract_Numbers
Step 2: Declare a parameter and name it as $input_field datatype(256)
Step 3: Change the datatype of parameter Return to varchar(256) [by default return type is int)
Step 4: Declare Local Variables as shown in left pane of the function editor.
$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
Step 5: Copy paste the following code in body of the function editor.
# This function is to Extract only numbers from a string.
$L_String =$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) )
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
end
Return replace_substr( replace_substr( rtrim_blanks( rtrim_blanks( $L_String_final )),' ',' '),' ', ' ');
Step 5: Save this function.
Step 6: Call this function while mapping your field in Query Editor where you want to apply this function.
Example: CF_Extract_Numbers(Table1.INPUT_VAL)
So your function would look like as below in editor.
Regards,
MS Ansari
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
81 | |
24 | |
11 | |
9 | |
7 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.