ONE Expressions Handbook

This guide describes how Ataccama ONE expressions are used throughout the platform for rules, data transformations, and more, and provides immediate practical guidance for using expressions.

What are ONE expressions?

ONE expressions are formulas that let you create validation conditions and transformations throughout the Ataccama platform. Expressions are built using functions - pre-defined operations that perform specific tasks like manipulating text, calculating dates, or evaluating conditions. An expression can be as simple as a single function (for example, upper(name) to convert text to uppercase) or combine multiple functions to create complex logic. Expressions are used in multiple contexts:

Rules: Used as condition logic to validate data.
Variables: Used to transform rule inputs before evaluation.
Transformation plans: Used in various steps including:
- Transform Data steps - To modify and calculate values.
- Filter conditions - To select specific records.
- Split conditions - To route data to different paths.

Expressions in ONE Desktop

ONE expressions are also used extensively in ONE Desktop for data processing steps.

Within ONE Desktop, expressions enable you to manipulate strings, work with dates, apply conditional logic, perform pattern matching with regular expressions, calculate string and set distances, and retrieve system parameters during plan execution.

If you use Microsoft Excel, you already understand expressions:

Concept Microsoft Excel ONE Expression

Concept	Microsoft Excel	ONE Expression
Text joining	`="ATA" & "CCAMA"` → `"ATACCAMA"`	`"ATA" + "CCAMA"` → `"ATACCAMA"`
Conditional logic	`=IF(A1>10, "High", "Low")`	`iif(AMOUNT>10, "High", "Low")`
Text functions	`=LEFT("ATACCAMA", 3)` → `"ATA"`	`left("ATACCAMA", 3)` → `"ATA"`
Date functions	`=TODAY()`	`today()`

Text joining

="ATA" & "CCAMA" → "ATACCAMA"

"ATA" + "CCAMA" → "ATACCAMA"

Conditional logic

=IF(A1>10, "High", "Low")

iif(AMOUNT>10, "High", "Low")

Text functions

=LEFT("ATACCAMA", 3) → "ATA"

left("ATACCAMA", 3) → "ATA"

Date functions

=TODAY()

today()

However, there are some key differences from Microsoft Excel:

Use + instead of & for text joining.
Use iif() instead of IF() for conditions.
Use substr() instead of MID() for text extraction. In addition, counting starts at 0, not 1.

The following sections provide examples of the most useful ONE expressions for common data tasks. For a list of all available functions and their syntax, see ONE Expressions Reference.

Your first expression: checking for empty fields

Let’s start with a common validation scenario. To check if a required field like CUSTOMER_NAME is empty or contains only spaces:

length(trim(CUSTOMER_NAME)) = 0

This expression works as follow (step-by-step):

CUSTOMER_NAME - Get the field value.
trim(CUSTOMER_NAME) - Remove leading and trailing spaces.
length(trim(CUSTOMER_NAME)) - Count remaining characters.
length(trim(CUSTOMER_NAME)) = 0 - Check if count equals 0 (empty).

In Microsoft Excel, the equivalent would be =LEN(TRIM(A1))=0.

Using the expression in a rule

In your rule interface, you would set:

Inputs:

String, CUSTOMER_NAME

Rule logic:

When Condition:
```
length(trim(CUSTOMER_NAME)) = 0
```
Then Result: Invalid
Explanation: IS_EMPTY

Essential functions

The following are some of the functions you might need most frequently:

Text processing functions

ONE function Purpose Example

ONE function	Purpose	Example
`trim(text)`	Remove extra spaces.	`trim(" hello ")` → `"hello"`
`upper(text)`	Convert to uppercase.	`upper("hello")` → `"HELLO"`
`lower(text)`	Convert to lowercase.	`lower("HELLO")` → `"hello"`
`left(text, count)`	Get first N characters.	`left("ATACCAMA", 3)` → `"ATA"`
`right(text, count)`	Get last N characters.	`right("ATACCAMA", 4)` → `"CAMA"`
`substr(text, start, length)`	Extract middle characters.	`substr("ATACCAMA", 2, 3)` → `"ACC"`
`length(text)`	Count characters.	`length("HELLO")` → `5`
`replace(text, old, new)`	Replace text.	`replace("hello", "l", "x")` → `"hexxo"`

trim(text)

Remove extra spaces.

trim(" hello ") → "hello"

upper(text)

Convert to uppercase.

upper("hello") → "HELLO"

lower(text)

Convert to lowercase.

lower("HELLO") → "hello"

left(text, count)

Get first N characters.

left("ATACCAMA", 3) → "ATA"

right(text, count)

Get last N characters.

right("ATACCAMA", 4) → "CAMA"

substr(text, start, length)

Extract middle characters.

substr("ATACCAMA", 2, 3) → "ACC"

length(text)

Count characters.

length("HELLO") → 5

replace(text, old, new)

Replace text.

replace("hello", "l", "x") → "hexxo"

substr() starts counting from 0.

Conditional logic functions

ONE Function Purpose Example

ONE Function	Purpose	Example
`iif(condition, true_value, false_value)`	Simple if-then logic.	`iif(AGE>=18, "Adult", "Minor")`
`case(cond1, val1, cond2, val2, default)`	Multiple conditions.	`case(GRADE>=90, "A", GRADE>=80, "B", "C")`
`nvl(value1, value2, …)`	Use first non-null value.	`nvl(NICKNAME, FIRST_NAME, "Unknown")`

iif(condition, true_value, false_value)

Simple if-then logic.

iif(AGE>=18, "Adult", "Minor")

case(cond1, val1, cond2, val2, default)

Multiple conditions.

case(GRADE>=90, "A", GRADE>=80, "B", "C")

nvl(value1, value2, …)

Use first non-null value.

nvl(NICKNAME, FIRST_NAME, "Unknown")

Date functions

ONE Function Purpose Example

ONE Function	Purpose	Example
`today()`	Current date.	`today()` → current date
`dateDiff(start, end, unit)`	Calculate date difference.	`dateDiff(BIRTH_DATE, today(), "YEAR")` → age
`dateAdd(date, value, unit)`	Add time to date.	`dateAdd(today(), -30, "DAY")` → 30 days ago
`datePart(date, unit)`	Extract date part.	`datePart(ORDER_DATE, "YEAR")` → year only

today()

Current date.

today() → current date

dateDiff(start, end, unit)

Calculate date difference.

dateDiff(BIRTH_DATE, today(), "YEAR") → age

dateAdd(date, value, unit)

Add time to date.

dateAdd(today(), -30, "DAY") → 30 days ago

datePart(date, unit)

Extract date part.

datePart(ORDER_DATE, "YEAR") → year only

Pattern matching with regular expressions

Regular expressions (regex) allow you to match complex text patterns. In ONE expressions, regex patterns are prefixed with @ and used with the matches() function.

Pattern Purpose Example

Pattern	Purpose	Example
`matches(@"pattern", text, true)`	Test if text matches pattern.	`matches(@"^[A-Z]{3}$", "USA", true)` → true
`@"^…"`	Start of text.	`@"^Hello"` matches "Hello world"
`@"…$"`	End of text.	`@"world$"` matches "Hello world"
`@"[A-Z]"`	Any uppercase letter.	`@"[A-Z]{2}"` matches "US"
`@"[0-9]"` or `@"\d"`	Any digit.	`@"\d{4}"` matches "2024"
`@".+"`	One or more of any character.	`@".+@.+"` simple email check
`@"(pattern1\|pattern2)"`	Either pattern.	`@"(USD\|EUR)"` matches USD or EUR

matches(@"pattern", text, true)

Test if text matches pattern.

matches(@"^[A-Z]{3}$", "USA", true) → true

@"^…"

Start of text.

@"^Hello" matches "Hello world"

@"…$"

End of text.

@"world$" matches "Hello world"

@"[A-Z]"

Any uppercase letter.

@"[A-Z]{2}" matches "US"

@"[0-9]" or @"\d"

Any digit.

@"\d{4}" matches "2024"

@".+"

One or more of any character.

@".+@.+" simple email check

@"(pattern1|pattern2)"

Either pattern.

@"(USD|EUR)" matches USD or EUR

Some common regex patterns used in the examples are:

Email: @"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$".
US phone: @"^$\d{3}$\s\d{3}-\d{4}$" for (123) 456-7890 format.
ISO country code: @"^[A-Z]{3}$" for 3-letter codes like USA.

Nesting functions

You can embed functions within functions by using brackets. For example:

`Upper(Left("AtaccamaONE", 8) & " Data Quality")`

This expression would return ATACCAMA DATA QUALITY in upper case. When functions are nested with brackets, they are evaluated from the innermost bracket (the function Left in this case), then any operations (in this case the concatenation &), and finally the outer function Upper (which converts text to upper case).

Expression examples

This section provides a compilation of example expressions for use across the various contexts mentioned earlier in the article. The examples are organized by type - transformations first, followed by detailed rule validation examples.

Transformation expressions

These expressions are used to transform data in variables or transformation plans. They can also be used together with rules—either as variables to transform rule inputs before validation, or after validation to extract additional information from validated data. See ISIN validation and parsing for an example of how transformations work together with validation rules.

Clean and format names

Converts name to proper capitalization.

trim(capitalize(lower(FULL_NAME)))

Remove special characters from customer name

Replaces all characters other than specified letters (including German umlauts), spaces, and selected punctuation (comma, dot, dash) with a space.

substituteAll("[^a-zA-ZäöüßÄÖÜ\\s',.-]", " ", CLIENT_NAME)

Combine fields

Creates a full address string from component fields.

full_address := trim(ADDRESS1 + ", " + CITY + ", " + STATE + " " + ZIP_CODE)

Compute age in months

Calculates the number of months between birth date and today.

dateDiff(birth_date, today(), 'MONTH')

Compute age in years

Calculates the number of years between birth date and today.

dateDiff(birth_date, today(), 'YEAR')

Convert Celsius to Fahrenheit

Standard formula for converting Celsius temperature to Fahrenheit.

(TEMPERATURE_CELSIUS * 1.8) + 32

Convert Fahrenheit to Celsius

Standard formula for converting Fahrenheit temperature to Celsius.

(TEMPERATURE_FAHRENHEIT - 32) / 1.8

Parse domain from URL

If the input string starts with "http://" or "https://", this expression extracts the domain by taking the substring between the first double forward slash ("//") and before the next forward slash ("/").

iif(find(@"^(?:https?://)?([^/]+)", in_URL),
    substr(in_URL, indexOf(in_URL, "//") + 2,
           indexOf(in_URL, "/", indexOf(in_URL, "//") + 2) - (indexOf(in_URL, "//") + 2)),
    in_URL)

Rule examples

These expressions are typically used in rule conditions to validate data quality.

The examples are presented in a format that mimics how you would configure them in the platform, showing the inputs (attributes) and rule logic (conditions) as they would appear when setting up rules.

Check for specific values

Validate that STATUS must be one of these values: "ACTIVE", "INACTIVE", "PENDING". Any other value, including null or empty strings, should be considered invalid.

Inputs

Attributes:
- type: String, value: STATUS

Rule Logic

Condition 1

When Condition:

upper(trim(STATUS)) NOT IN {"ACTIVE", "INACTIVE", "PENDING"}

Then Result: Invalid
Explanation: STATUS is not one of the allowed values

Condition 2

When Condition:

STATUS is null OR length(trim(STATUS)) = 0

Then Result: Invalid
Explanation: STATUS is null or empty

Fallback condition

IF none of the conditions above apply THEN: Valid

Simple number validation

Validate that amount must be positive. This ensures that financial amounts, quantities, or other numeric values that should never be negative or zero are properly validated.

Inputs

Attributes:
- type: Integer, value: AMOUNT

Rule Logic

Condition 1

When Condition:
```
AMOUNT <= 0
```
Then Result: Invalid
Explanation: Amount is not positive

Condition 2

When Condition:
```
AMOUNT is null
```
Then Result: Invalid
Explanation: Amount is null

Fallback condition

IF none of the conditions above apply THEN: Valid

Null or placeholder value detection

Detect missing or placeholder data. This expression checks if a value represents missing, null, or placeholder data. It trims whitespace and checks against common null indicators including various cases of "NULL", "N/A", single punctuation marks, and empty strings.

Inputs

Attributes:
- type: String, value: VALUE

Rule Logic

Condition 1

When Condition:

trim(VALUE) is in {'NULL', 'Null', 'null', '.', ',', '-', '_', '', 'N/A', 'n/a', 'na', 'NA'}

Then Result: Invalid
Explanation: Value is a placeholder or null indicator

Fallback condition

IF none of the conditions above apply THEN: Valid

Pattern matching rules

Pattern matching only validates the format of values (for example, checking if a phone number follows the pattern 123-456-7890). To ensure data validity, combine pattern matching with additional checks such as domain or reference data validation. For instance, a phone number might match the correct format but still be invalid if it doesn’t exist in your reference data or uses an invalid area code.

Email format validation

Validate email format using regular expressions.

This expression uses a regex pattern to check if the email follows standard format requirements—ensuring it has a username part, an at symbol ("@"), and a domain part with at least two letters.

Inputs

Attributes:
- type: String, value: EMAIL_ADDRESS

Rule Logic

Condition 1

When Condition:

NOT matches(@"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$", EMAIL_ADDRESS, true)

Then Result: Invalid
Explanation: Email format is invalid

Condition 2

When Condition:

EMAIL_ADDRESS is null OR length(trim(EMAIL_ADDRESS)) = 0

Then Result: Invalid
Explanation: Email address is null or empty

Fallback condition

IF none of the conditions above apply THEN: Valid

Phone number format validation

US phone number format

Validate US phone number patterns using regular expressions.

This rule uses regex patterns to check if a phone number matches one of three valid US formats: parentheses with area code (for example, (123) 456-7890), dashes (123-456-7890), or 10 consecutive digits (1234567890).

Inputs

Attributes:
- type: String, value: PHONE

Rule Logic

Condition 1

When Condition:

NOT (matches(@"^\(\d{3}\)\s\d{3}-\d{4}$", PHONE, true) OR
matches(@"^\d{3}-\d{3}-\d{4}$", PHONE, true) OR
matches(@"^\d{10}$", PHONE, true))

Then Result: Invalid
Explanation: Phone number does not match any valid US format

Condition 2

When Condition:

PHONE is null OR length(trim(PHONE)) = 0

Then Result: Invalid
Explanation: Phone number is null or empty

Fallback condition

IF none of the conditions above apply THEN: Valid

UK phone number format

Validate UK phone number format using regular expressions.

This rule uses a regex pattern to validate UK phone numbers which can start with +44 (international), 0 (domestic), or neither. The country code can optionally be in parentheses, and the number must contain exactly 10 digits with optional spaces between digits.

Inputs

Attributes:
- type: String, value: PHONE

Rule Logic

Condition 1

When Condition:

NOT matches(@"^(\+44\s?|\(\+44\)\s?|0)?[1-9]\d{9}$", replace(PHONE, " ", ""), true)

Then Result: Invalid
Explanation: Phone number does not match UK format

Condition 2

When Condition:

PHONE is null OR length(trim(PHONE)) = 0

Then Result: Invalid
Explanation: Phone number is null or empty

Fallback condition

IF none of the conditions above apply THEN: Valid

Date range validation

Validate that birth date is reasonable. This expression checks if a person’s birth date is between January 1, 1900, and today’s date.

Inputs

Attributes:
- type: Date, value: BIRTH_DATE

Rule Logic

Condition 1

When Condition:

BIRTH_DATE < toDate("1900-01-01", "yyyy-MM-dd") OR BIRTH_DATE > today()

Then Result: Invalid
Explanation: Birth date is outside reasonable range

Condition 2

When Condition:
```
BIRTH_DATE is null
```
Then Result: Invalid
Explanation: Birth date is null

Fallback condition

IF none of the conditions above apply THEN: Valid

ISO-3 country code format validation

Validate 3-letter country code format using regular expressions.

This rule uses a regex pattern to ensure country codes are exactly three alphabetic characters (like USA, GBR, FRA). It rejects numeric values and values that aren’t exactly three characters long.

Inputs

Attributes:
- type: String, value: COUNTRY_CODE

Rule Logic

Condition 1

When Condition:

NOT matches(@"^[A-Z]{3}$", upper(COUNTRY_CODE), true)

Then Result: Invalid
Explanation: Country code is not 3 uppercase letters

Condition 2

When Condition:

COUNTRY_CODE is null OR length(trim(COUNTRY_CODE)) = 0

Then Result: Invalid
Explanation: Country code is null or empty

Fallback condition

IF none of the conditions above apply THEN: Valid

Complex business logic rules

Healthcare provider licensing

Validate that license numbers are correctly associated with provider types.

Healthcare Providers (HCP) must have a license number, while Healthcare Organizations (HCO) should not have one. This rule ensures proper data entry based on the provider type.

Inputs

Attributes:
- type: String, value: PROVIDER_TYPE
- type: String, value: LICENSE_NUMBER

Rule Logic

Condition 1

When Condition:

upper(trim(PROVIDER_TYPE)) = "HCO" AND LICENSE_NUMBER is not null AND length(trim(LICENSE_NUMBER)) > 0

Then Result: Invalid
Explanation: HCO should not have a license number

Condition 2

When Condition:

upper(trim(PROVIDER_TYPE)) = "HCP" AND (LICENSE_NUMBER is null OR length(trim(LICENSE_NUMBER)) = 0)

Then Result: Invalid
Explanation: HCP must have a license number

Condition 3

When Condition:

PROVIDER_TYPE is null OR length(trim(PROVIDER_TYPE)) = 0

Then Result: Invalid
Explanation: Provider type is missing

Fallback condition

IF none of the conditions above apply THEN: Valid

Customer identification requirements

Validate that customers have the appropriate identification based on their type.

Business customers must have a Tax ID and should not have an SSN. Individual customers must have an SSN and should not have a Tax ID. This rule ensures the correct identifier is provided depending on the type of customer.

Inputs

Attributes:
- type: String, value: CUSTOMER_TYPE
- type: String, value: TAX_ID
- type: String, value: SSN

Rule Logic

Condition 1

When Condition:

CUSTOMER_TYPE is null OR length(trim(CUSTOMER_TYPE)) = 0

Then Result: Invalid
Explanation: Customer type is missing

Condition 2

When Condition:

upper(trim(CUSTOMER_TYPE)) IN {"BUSINESS", "COMPANY"}
AND (TAX_ID is null OR length(trim(TAX_ID)) = 0)

Then Result: Invalid
Explanation: Business customer is missing Tax ID

Condition 3

When Condition:

upper(trim(CUSTOMER_TYPE)) IN {"INDIVIDUAL", "PERSON"}
AND (SSN is null OR length(trim(SSN)) = 0)

Then Result: Invalid
Explanation: Individual is missing SSN

Condition 4

When Condition:

upper(trim(CUSTOMER_TYPE)) IN {"BUSINESS", "COMPANY"}
AND (SSN is not null OR length(trim(SSN)) > 0)

Then Result: Invalid
Explanation: Business customer has an SSN

Condition 5

When Condition:

upper(trim(CUSTOMER_TYPE)) IN {"INDIVIDUAL", "PERSON"}
AND (TAX_ID is not null OR length(trim(TAX_ID)) > 0)

Then Result: Invalid
Explanation: Individual has a Tax ID

Fallback condition

IF none of the conditions above apply THEN: Valid

Financial identifier validation & parsing

CUSIP validation

Validate CUSIP (Committee on Uniform Securities Identification Procedures) identifiers. A CUSIP is a 9-character alphanumeric code uniquely identifying North American financial securities.

This rule validates the format and verifies the check digit using the standard CUSIP algorithm to ensure the identifier hasn’t been corrupted or mistyped.

Inputs

Attributes:
- type: String, value: CUSIP_VALUE

Rule Logic

Condition 1

When Condition:

cusip_uc := upper(CUSIP_VALUE);
cusip_left := left(cusip_uc,8);
alphabet := "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
alphabet_wspace := "A B C D E F G H I J K L M N O P Q R S T U V W X Y Z";
odds:=trim(substituteAll(@"(.).", "$1 ", cusip_left));
evens:=trim(substituteAll(@".(.)", "$1 ", cusip_left));
oddToAdd := set.mapExp(odds, " ", (x){
    case(
        isNumber(x), x,
        set.contains(alphabet_wspace, x, " "), toString((indexOf(alphabet, x)+10)),
        x is '*', '36',
        x is '@', '37',
        x is '#', '38',
        '0'
    )
});
evenToAdd := set.mapExp(evens, " ", (x){
    case(
        isNumber(x), toString(toInteger(x)*2),
        set.contains(alphabet_wspace, x, " "), toString((indexOf(alphabet, x)+10)*2),
        x is '*', '72',
        x is '@', '74',
        x is '#', '76',
        '0'
    )
});
toAdd := set.mapExp(oddToAdd+" "+evenToAdd, " ", (x){
    toString(
        math.floor(toInteger(x)/10)+toInteger(x)%10
    )
});
total := set.sumExp(toAdd, " ", (x){toInteger(x)});
check := (10 - (total % 10))%10;
toString(check) != right(cusip_uc, 1)

Then Result: Invalid
Explanation: CUSIP check digit validation failed

Condition 2

When Condition:

CUSIP_VALUE is null OR length(trim(CUSIP_VALUE)) = 0

Then Result: Invalid
Explanation: CUSIP value is null or empty

Fallback condition

IF none of the conditions above apply THEN: Valid

ISIN validation and parsing

Validate ISIN (International Securities Identification Number). An ISIN is a 12-character alphanumeric code consisting of a two-letter country code, nine-character NSIN (National Securities Identifying Number), and one check digit.

This rule uses a regex pattern to validate the format (2 letters + 9 alphanumeric + 1 digit) and additionally verifies the check digit using the ISIN algorithm (Luhn variant for alphanumeric) to ensure data integrity.

The transformation expression after the example can be used after validation to extract the country code from a valid ISIN.

Inputs

Attributes:
- type: String, value: ISIN_VALUE

Rule Logic

Condition 1

When Condition:

value:=replace(trim(ISIN_VALUE), '-', '');
function digitSum(integer digit) {set.sumExp(trim(substituteAll("(.)", "$1 ", toString(digit))), " ", (x) {toInteger(x)})}
checknum:=right(value,1);
rest:=left(value,length(value)-1);
final:=replace(set.mapexp(substituteAll("(.)", "$1 ", rest), " ",
    (x) {decode(x,'A','10','B','11','C','12','D','13','E','14','F','15','G','16','H','17','I','18','J','19','K','20','L','21','M','22','N','23','O','24','P','25','Q','26','R','27','S','28','T','29','U','30','V','31','W','32','X','33','Y','34','Z','35',x)})," ","");
checksum:= iif( length(final)%2=0,
    set.sumExp(trim(substituteAll("(.).", "$1 ", final)) , " ", (x) {toInteger(x)}) +
    set.sumExp(trim(substituteAll(".(.)", "$1 ", final)) , " ", (x) {digitSum(toInteger(x)*2)}),
    set.sumExp(trim(substituteAll(".(.)", "$1 ", final+0)) , " ", (x) {toInteger(x)}) +
    set.sumExp(trim(substituteAll("(.).", "$1 ", final+0)) , " ", (x) {digitSum(toInteger(x)*2)}));
tostring((10-(checksum%10))%10) != checknum

Then Result: Invalid
Explanation: ISIN check digit validation failed

Condition 2

When Condition:

ISIN_VALUE is null OR length(trim(ISIN_VALUE)) = 0

Then Result: Invalid
Explanation: ISIN value is null or empty

Condition 3

When Condition:

length(replace(trim(ISIN_VALUE), '-', '')) != 12

Then Result: Invalid
Explanation: ISIN must be exactly 12 characters

Condition 4

When Condition:

NOT matches(@"^[A-Z]{2}[A-Z0-9]{9}[0-9]$", upper(replace(trim(ISIN_VALUE), '-', '')), true)

Then Result: Invalid
Explanation: ISIN format is invalid (must be 2 letters + 9 alphanumeric + 1 digit)

Fallback condition

IF none of the conditions above apply THEN: Valid

If the trimmed code has the right length of 12 characters, extract the first two characters as country code. Otherwise return empty string.

This transformation can be used as a variable to pre-process the ISIN or after validation to extract additional information.

iif(length(replace(trim(ISIN_VALUE), '-', '')) = 12, left(ISIN_VALUE, 2), '')

Government identifier validation

UK national insurance number (NINO)

Validate UK national insurance numbers using regular expressions and pattern matching. A valid NINO has the format: two letters + six digits + one letter (for example, AB123456C).

The rule uses regex to check that the first two letters don’t contain invalid characters (D, F, I, Q, U, V, O) or specific invalid combinations (BG, GB, KN, NK, NT, TN, ZZ). The final letter must be A, B, C, or D. This ensures NINOs follow the official UK government format requirements.

Inputs

Attributes:
- type: String, value: NINO_VALUE

Rule Logic

Condition 1

When Condition:

removeNino:=iif(left(upper(NINO_VALUE),5)="NINO:",replace(upper(NINO_VALUE),"NINO:",""),upper(NINO_VALUE));
twoLetter:= left(squeezeSpaces(removeNino),2);
twoLetterCheck:= find(@"\w[dfiquvo]|[dfiquv]\w",twoLetter,true);
sixNumber:= length(trashNonDigits(upper(NINO_VALUE)))=6;
finalLetter:= right(upper(NINO_VALUE),1);
check1:=twoLetterCheck is true or twoLetter is in {"BG", "GB", "KN", "NK", "NT", "TN","ZZ"};
check2:= sixNumber is false;
check3:=finalLetter is not in {'A','B','C','D'};
true is in {check1,check2,check3}

Then Result: Invalid
Explanation: NINO format validation failed

Condition 2

When Condition:

NINO_VALUE is null OR length(trim(NINO_VALUE)) = 0

Then Result: Invalid
Explanation: NINO value is null or empty

Fallback condition

IF none of the conditions above apply THEN: Valid

Was this page useful?

ONE Expressions Handbook

What are ONE expressions?

Your first expression: checking for empty fields

Using the expression in a rule

Essential functions

Text processing functions

Conditional logic functions

Date functions

Pattern matching with regular expressions

Nesting functions

Expression examples

Transformation expressions

Clean and format names

Remove special characters from customer name

Combine fields

Compute age in months

Compute age in years

Convert Celsius to Fahrenheit

Convert Fahrenheit to Celsius

Parse domain from URL

Rule examples

Check for specific values

Simple number validation

Null or placeholder value detection

Pattern matching rules

Email format validation

Phone number format validation

US phone number format

UK phone number format

Date range validation

ISO-3 country code format validation

Complex business logic rules

Healthcare provider licensing

Customer identification requirements

Financial identifier validation & parsing

CUSIP validation

ISIN validation and parsing

Related transformation: parse country code from ISIN

Government identifier validation

UK national insurance number (NINO)