NonNumeric Characters




  • Alan Mark

    Hi Wedmore,

    If you just want to drop these records - the easiest way to do that is in your Data Link or Import Job on the Define Fields step.

    Change the column to Integer, then scroll down to the How to handle invalid data? section, expand it, and make sure it's set to Drop record.

    If you want to keep these columns, you'll need to use a nested IF CONTAINS_IC with a Regex such as [A-Za-z] to identify, then either drop alphanumerics or convert them into numbers.  I caution against converting to numbers unless you have another way to identify them as contractors - while unlikely - it would be possible to end up with a non-unique value.

    Note, IF and CONTAINS_IC are clickable above and will send you over to the documentation pages for these functions.

    If you need to qualify the regex to use, I commonly use the site regex101.

    Comment actions Permalink
  • Elwyn Roberts

    Thanks Alan.  I went with the import/drop record solution since I wouldn't be able to use/join on non-numeric regardless.

    Comment actions Permalink
  • Elwyn Roberts

    Its been a couple of years, but this issue came up again and here is a solution I came up with.  It seems to be quite effective:

    IF(COUNTMATCHES(#columnname;'[0-9]') == LEN(#columnname) ; INT(#columnname) ; 0)

    To explain:  In the string column, it will count the number of characters that are 0 to 9.  It will then compare that to the length of the string (you should probably put a TRIM around this) and if its the same as the number of numeric characters, its a numeric value. 

    So when true, output the INT version of the string.  When false, output 0.  So now you have a column that is the numeric value of a string column. 

    This worked really well for client numbers and account numbers.

    If anyone sees any issues with this approach, let me know.

    Comment actions Permalink
  • Konsta Danyliuk

    Hi Erwyn.
    I like the approach.

    If you would like to put an empty value instead of 0 for accounts that contain non-numeric characters, you could use null instead of 0 at the ELSE condition.

    IF(COUNTMATCHES(#columnname;'[0-9]') == LEN(#columnname); INT(#columnname); null)
    Comment actions Permalink
  • D. Olsson

    Thanks people, this is an awesome solve

    Comment actions Permalink

Please sign in to leave a comment.