Is there an easy way to filter out records which contain a number only (for a given column)?
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

Hi Dan
To number is a conversion Function, similar to To date and To Alphanumeric:
It will convert a string value "12" to a number 12. If it runs into a nonnumeric 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
1 
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
2
Answers

Thanks both!
Regex solution:
1