NonNumeric Characters

Answered

Follow

Elwyn Roberts

• April 14, 2018 22:10

I have pulled in a dataset which has the employee login which should be 6 numerics.  However, the data includes some nonnumeric characters (maybe contractors?) and so the column is STRING and not INTEGER.  

If I convert the column using INT(<column>) it will fail.

I couldnt find a function to test for Numerics... or maybe I am looking in the wrong place.  But I need to filter out these non numerics rows in DataMeer so I can join it onto another sheet which has the join field as numeric.

1

• 

  Alan

  • April 14, 2018 22:51

  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.

  1

  Comment actions Permalink
• 

  Elwyn Roberts

  • April 15, 2018 08:02

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

  0

  Comment actions Permalink
• 

  Elwyn Roberts

  • May 01, 2020 03:04

  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.

  1

  Comment actions Permalink
• 

  Konsta Danyliuk

  • May 01, 2020 11:38

  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)

  1

  Comment actions Permalink
• 

  D. Olsson

  • January 10, 2023 17:26

  Thanks people, this is an awesome solve

  2

  Comment actions Permalink

Please sign in to leave a comment.