Is there an easy way to filter out records which contain a number only (for a given column)?

Dan Mason

I need to filter out records which contain a number only from an address column.

I have attempted to do this using the To Number and Is Number functions: 

This approach does not work because the To Number function does not behave as I would expect it to:

Why does 2 Street and 4Street not return the Invalid number error?

Can a description of To number be found somewhere in the documentation? 

I’m aware that this could be achieved using a regular expression function, but is there an easier way? 

Thanks in advance :)

Josh Boxer

Hi Dan

To number is a conversion Function, similar to To date and To Alphanumeric:

https://docs.experianaperture.io/data-quality/aperture-data-studio-v2/get-started/create-functions/#conversion~native-functions

It will convert a string value "12" to a number 12. If it runs into a non-numeric value then it will ignore the rest of the input

You could compare the length of the input and the result. If the lengths match then the input contains only numbers.

Probably a few other ways of achieving this

Henry Simms

Hi @Dan Mason

The To number function is unusual in that it can extract and parse a portion of the input value rather than the whole value. Other Conversion functions don't do that, they just look at the whole input value. I've written documentation on To number which should appear on the docs site in the next few weeks:

To solve your problem, I can't think of a better approach than to use Josh's suggestion, but to also account for leading whitespace (" 12) and leading zeros ("034"), both of which you'd probably class as "number only values", and would successfully convert to number, but would fail the length check.

I think using a regular expression is probably the way to go here. If you have a solution using regex can you post it?

Henry

Dan Mason

Thanks both!

Regex solution: