cancel
Showing results for 
Search instead for 
Did you mean: 

HOW TO EXTRACT NUMBER FROM ALPHANUMERIC?

Former Member
0 Kudos

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!

Accepted Solutions (1)

Accepted Solutions (1)

former_member186897
Contributor
0 Kudos

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

Answers (2)

Answers (2)

akhileshkiran
Contributor
0 Kudos

Hi

you can simply use String Function. Search_Replace you can achieve this.

Output:

Regards,

Akhileshkiran.

Former Member
0 Kudos