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

Dan MasonDan Mason Experian Employee
edited September 13 in General discussion

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 :)

Best Answers

  • Josh BoxerJosh Boxer Administrator
    edited September 13 Answer ✓

    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 SimmsHenry Simms Experian Super Contributor
    Answer ✓

    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

Answers

Sign In or Register to comment.