Identify non-numeric values

dataservices job design
1

Hello,

I have a field which contains both numeric and non-numeric values. I need to output the numeric value and change the non-numeric value to some number like 9999999.

I tried is_valid_in() and match_regex(), but none of them are working.

For example if i use the below, my output will be 99 and not the input numeric value. Same result if i use is_valid_int().

ifthenelse(match_regex(Case_default.PRICE,’[0-9]’,NULL)=1,Case_default.PRICE,‘99’)

The problem is the non-numeric values can be anything like N/C, TBD etc no particular pattern.

Any ideas would be highly appreciated.

Thanks,
Arun

Arun.K :us: (BOB member since 2011-10-18)

2

Solved!!

Arun.K :us: (BOB member since 2011-10-18)

3

Normally when you resolve something after asking the question you post the solution so that others can benefit. Please post the solution (even if it makes you look a bit silly).

eganjp :us: (BOB member since 2007-09-12)

4

Yes, i should have done it.

I did it through is_valid_int() function instead of match_regex(). The expression is like this.

ifthenelse(is_valid_int(Case_default.PRICE,‘000000.00’)=1,Case_default.PRICE,‘99’)

Arun

Arun.K :us: (BOB member since 2011-10-18)

5

Thanks for posting your solution!

The regex should work if you include the parameter to look for the complete input, not only a partial match.

Is_valid_int is working fine, but there is a limit to the amount of number places it validates.

Is_valid_double can check on more numbers.

Johannes Vink :netherlands: (BOB member since 2012-03-20)

6

Thanks Johannes for the input.

Arun

Arun.K :us: (BOB member since 2011-10-18)