What is the best way to check for the presence of a number value in a field?

Payal

We often have a requirement from clients to check for numbers in a name field or for name in a phone number field. What is the easiest way to do this?

Sueann See

@Payal i'm not sure if there is a better way to do this, but this is one way to do it:

Using a transformation step, create a new column called "Name Contains Digits" and create your own function as follows:

• you remove digits from the Name and then compare it back to the original Name to determine if they are the same.
• you invert the output such that if there is a difference, it would reflect as "True".

Similarly, you create another new column called "Phone Contains Alphabet" and create your own function as follows:

• you remove alphabetic from the Phone Number and then compare it back to the original Phone NUmber to determine if they are the same.
• you invert the output such that if there is a difference, it would reflect as "True".

The Result:

See that i intentionally use some vanity phone numbers as examples. I am not sure if you would consider them as invalid phone numbers in your use case.

Henry Simms

@Payal here's a couple of other functions you might want to consider:

Extract integers to pull out any numbers from a string (or returns null if there are none)

Format pattern to get the format (where any number is replaced by "9", any letter replaced by "A"), and then a simple contains check. If the format pattern contains "9", then there's at least one integer in the string:

Payal

@Henry Simms @Sueann See thank you so much for your responses. both great solutions and very different in the approaches.