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

Payal
Payal Experian Employee
edited January 5 in General

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?

Tagged:

Answers

  • Sueann See
    Sueann See Experian Super Contributor

    @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".
    image.png


    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".


    image.png


    The Result:

    image.png


    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
    Henry Simms Administrator

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

    image.png

    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:

    image.png


  • Payal
    Payal Experian Employee

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

Categories