1

The HISTORY_MEMBERS table has undesireable entries in the some of the columns. The majority are in the Last_Name column. Some entries were made for testing (TestLastName, test, tester, etc.) and others have non-alpha characters (!, ., /, 9, etc.) I'm tasked with determining how many there are so we can determine what to do about them, if anything. I'm a fairly new to the field and brand new to Teradata and my usual REGEXP is not functioning in this environment. Can someone help?

SELECT * FROM HISTORY_MEMBER
WHERE Last_Name REGEXP '^[A-Za-z]+$' OR
Last_Name LIKE 'test%' or
LENGTH(Last_Name) <2;

I've tried removing the carat and using NOT, no difference. I'm getting 3707 and when I try to resolve that I get 3706.

nbk
  • 45,398
  • 8
  • 30
  • 47
DLPP
  • 11
  • 1
  • you want https://docs.teradata.com/r/Teradata-VantageTM-SQL-Functions-Expressions-and-Predicates/March-2019/Regular-Expression-Functions/REGEXP_SIMILAR – nbk Jun 23 '23 at 22:33
  • Thank you. Can you elaborate? I'm unsure how this would work. – DLPP Jun 26 '23 at 14:16
  • 1
    To find a column containing non-alpha characters I would use `where regexp_instr(,'[^A-Za-z]',1) > 0` – Andrew Jun 26 '23 at 14:21
  • Or `REGEXP_SIMILAR(Last_Name,'[A-Za-z]+')=1` would be the direct equivalent (effectively, a leading ^ and trailing $ are assumed for REGEXP_SIMILAR). – Fred Jun 26 '23 at 18:35

0 Answers0