Unwated data in Dim Table

Comments

• Jim_Knicely - Select Field - Administrator

  November 2018

  Do the useless entries follow some sort of pattern? That is, do they all have that hyphen in them? If so, wouldn't a simple DELETE work?

  dbadmin=> SELECT * FROM dim_user;
       user_name
  -------------------
   JIM
   JANE
   Actionid-xxxxxxxx
   JOSH
   HELEN-AHGHGD
  (5 rows)
  
  dbadmin=> DELETE FROM dim_user WHERE INSTR(user_name, '-') > 1;
   OUTPUT
  --------
        2
  (1 row)
  
  dbadmin=> SELECT * FROM dim_user;
   user_name
  -----------
   JIM
   JANE
   JOSH
  (3 rows)
  

  0
• JunaidKhan ✭

  November 2018

  Yes Jim the pattern is like UCA EBC - ActionId: -

  0
• Jim_Knicely - Select Field - Administrator

  November 2018 edited November 2018

  If you don't want bad values to be inserted into a table column, add a "Check Constraint". For the constraint's Boolean expression, you use a Regular Expression to make sure the correct format is being used.

  Below is a general example showing how to only permit valid 10-digit phone numbers with hyphens to be added to a table... I'd try to give you a better example using your pattern, but I'm not sure what the hyphens are supposed to represent

  dbadmin=> CREATE TABLE only_usa_phone_number (phone_number VARCHAR(15), CONSTRAINT phone_number_check CHECK (REGEXP_LIKE(phone_number, '^\d{3}-\d{3}-\d{4}$')));    
  CREATE TABLE
  
  dbadmin=> INSERT INTO only_usa_phone_number SELECT '123-456-7890';
   OUTPUT
  --------
        1
  (1 row)
  
  dbadmin=> INSERT INTO only_usa_phone_number SELECT '1234567890';
  ERROR 7230:  Check constraint 'public.only_usa_phone_number.phone_number_check' regexp_like(only_usa_phone_number.phone_number, E'^\\d{3}-\\d{3}-\\d{4}$') violation: 'phone_number=1234567890'
  

  See:
  https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Constraints/ConstraintTypes/CheckConstraints.htm

  https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/RegularExpressions/REGEXP_LIKE.htm

  0