Objective

Verify that the data in a column conforms to a specific format (eg. $A$$A) where $ represents a numeric digit between 0-9 and A represents a character. For example, lets consider a flat file with a column called "Detrecid".

Solution
This can achieved by creating a Custom SQL rule in the data rules test plan. 

Step 1

Add a SQL Query Rule as shown below:

Step 2

Use the REGEXP_REPLACE () function to replace the characters with 'A' and numbers with '$'. Sample query for identifying records that do not follow this format is shown below:

select employer_name, REGEXP_REPLACE(REGEXP_REPLACE(employer_name,'[[:digit:]]','$','g') ,'[[:alpha:]]','A','g')
replace_number_with$ from t_comp_1899_5040
where REGEXP_REPLACE(REGEXP_REPLACE(employer_name,'[[:digit:]]','$','g') ,'[[:alpha:]]','A','g')!='$A$$A'