Sample rule expressions

These samples demonstrate how you can combine the building blocks for rule logic.

You can copy the provided expressions into your own data quality definitions and adjust them as needed.

AddressExists

Checks whether an address exists (null and blank value check)

Address EXISTS AND len(trim(Address)) <> 0

AddressTypeIfExistsThenIsInReferenceList

Checks whether an address type exists and is in the reference list.

AddressType EXISTS AND len(trim(AddressType)) <> 0 AND rtrim(AddressType) IN_REFERENCE_LIST {'S','R','B','L','G','U','O'}

AdultInRangeCalc

Checks whether the age is in the range 18-125. The expression is applied to the derived age calculated as the absolute value of the difference between the current date and date of birth.

abs(datediff(date(),DateOfBirth)) / 365 >= AdultAge AND abs(datediff(date(),DateOfBirth)) / 365 < MaxAge

ChildNotMarriedCalc

Check whether a person is a child (derived age) and if so, sets the marital status to no. The expression is applied to derived age calculated as the absolute value of the difference between the current year and year of birth.

IF abs(datediff(date(),DateOfBirth)) / 365 < AdultAge THEN MaritalStatus = 'N'

CreditCardMatchesRegex

Checks whether credit card numbers match a specific format. The expression is applied to credit card numbers in string format.

CreditCard MATCHES_REGEX '^(?:4[0-9]{12}(?:[0-9]{3})?|5[1-5][0-9]{14}|6(?:011|5[0-9][0-9])[0-9]{12}|6(?:22|4[4-9])[0-9]{13}|65[0-9]{14}|3[47][0-9]{13}|3(?:0[0-5]|[68][0-9])[0-9]{11}|(?:2131|1800|35\\d{3})\\d{11})$'

SsnMatchesHyphenFormat

Checks whether a social security number (SSN) matches a specific numeric format with hyphens. The expression is applied to string SSN values.

SSN MATCHES_FORMAT '999-99-9999'

TextSubstrInRefList

Checks whether the substring of a text value starting at position 3 for length 3 is in the provided reference list. This expression is applied to string data.

substring(TextField, 3, 3) IN_REFERENCE_LIST {'AAA','AAB','BAA','CCC'}

Learn more

Parent topic: Managing data quality definitions