Convert Varchar2 to Number datatype

[kamkaro]

Hi All,

Problem:
In my store procedure, I have a column ABC which data-type has varchar2. Column ABC contain numeric and non-numeric values. I would like to convert varchar2 to in Number. When I am using to_Number() function, it throw me "invalid Number" error. When I used "regexp_like(ABC, '^[+-]?([0-9]*\.?[0-9]+|[0-9]+\.?[0-9]*)([eE][+-]?[0-9]+)?$') " function, working fine in oracle but when I am executing same query in crystal, getting same invalid error.

Below is the column 'ABC' data
87632.98
AKDD-sks
12-09-2012
3876
987634

Requirement:
If there is Non_Numeric values in column ABC then change to Null and rest simple display.


What should I do to be able to convert all fields to number in order to execute in crystal (executing in Toad (oracle) is fine).


Thank you all in advance!

 

[pmax9999]

It is not clear to me whether you are using that code within the Stored Proc, or are trying to use the code within Crystal?

If it is the SP that contains the code I would see no reason why CR would have any impact on it. CR would only see the dataset returned by the database server after executing the SP, not the code that creates the SP.

If your SP is returning Column ABC with the data in your example above, simply add your code to the SP so as to return an additional column with the data as required by CR.

You may be able to get the code to work in a SQL Expression (I don't have access to Oracle to do any testing) but I don't think there will be an easy way to do it using Crystal functions to strip out the non numeric characters. The only way I can see off the top of my head would be to loop through the entire string, check whether the character is numeric and replace it with an empty string I it is not.

Hope this helps.

Cheers
Pete

 

[pmax9999]

Getting the database to do the heavy lifting is going to be much more efficient, but because it looked like an interesting challenge I thought I would give it a whirl in Crystal (I really do need to get out more). This is the code I came up with that will strip everything non-numeric out of a string field:

WhilePrintingRecords;
Local StringVar S := '';
Local StringVar Array X;
Local NumberVar i := 1;
    
    For i := 1 to Len({Table.StringField}) Step 1 Do
    S := S + {Table.StringField}[i] + ',';

X := Split(Left(S,(Len(S)-1)), ',');

i := 1;
S := '';

    For i := 1 to Len({Table.StringField}) Step 1 Do
    If      IsNumeric(X[i])
    Then    S := S + X[i]
    Else    S := S;

S

Not certain if a decimal point (ie ".") should be treated as numeric or not. If so, the code would need to be amended to also include those too.

Cheers
Pete

 

[pmax9999]

Or, simpler still:

WhilePrintingRecords;
Local StringVar S := '';
Local NumberVar i := 1;
    
    For i := 1 to Len({Table.stringField}) Step 1 Do

    If      IsNumeric({Table.stringField}[i])
    Then    S := S + {Table.stringField}[i]
    Else    S := S;

S

Pete