Validation types

No-code data validations

See below for details on all no-code data validations supported by OneSchema.


ValidationFunctionAutofixError code
AlphabeticalOnly allow alphabetical characters, spaces, and special characters (i.e. ñ and ü)N/A37
CountryCountries, dependent territories, and other.Auto-transforms between two-digit codes, three-digit codes, and full country names (i.e. US, USA, United States of America).51
Currency CodeISO codes for different currencies.N/A6
Custom RegexSupply a custom regex to validate values.N/A36
Date DD/MM/YYYYDates in DD/MM/YYYY format separated by slashes which require leading zeros.See dates table below.25
Date DD/MMM/YYDates in DD/MMM/YY separated by slashes which require leading zeros.See dates table below.35
Date ISODates in format YYYY-MM-DD (leading zeros).See dates table below.29
Date MM/DD/YYYYDates in MM/DD/YYYY format separated by slashes which require leading zeros.See dates table below.3
Date YYYY/MM/DDDates in YYYY/MM/DD format separated by slashes which require leading zeros.See dates table below.34
Datetime ISODates in format YYYY-MM-DDTHH:NNTz (timezone).See datetimes table below.30
Datetime MM/DD/YYYY HH:MMDate with time in format MM/DD/YYYY HH:mm.See datetimes table below.45
DomainValues must be valid website domains.N/A10
EmailValues must be an email.N/A13
File NameList of acceptable file name extensions (eg. .doc or .pdf).N/A48
IANA Time ZoneIANA time zone in the format Country/City (eg. America/New_York)N/A33
MoneyAmount of Money starting with dollar sign (ie. $123.45)Adds '$' to numbers with up to two decimal places. Adds trailing zero(s) until two decimal digits exist.31
NumberNumeric values: options for integer only, specific number of decimal places, and enforcing a range.N/A2
PercentageNumeric values that end with a percentage sign (%).Adds '%' to numbers. Adds '0' before decimal place is the number is <1.40
Phone NumberInternational e164 format (eg. +14087771288) between 2 and 15 digits.Adds a leading '+' if one is not present. Removes the following characters: . , / ( ) " " (space).7
PicklistValues from a set list of defined options.N/A1
SSN (masked)SSN with only the last 4 digits (eg. XXX-XX-6789).Will add '-' and mask the first 5 digits of a 9-digit number so the output matches the format XXX-XX-6789.42
SSN (unmasked)SSN with all 9 digits (eg. 123-45-6789).Will add '-' to a 9-digit number so the output matches the format 123-45-6789.43
TextAllow any input.N/AN/A
Time HH:MMMilitary 24hr time in format HH:mm (eg. 22:30).N/A26
Unit of MeasurementISO codes used for units of measure for intl. trade.N/A5
Unix TimestampNumber of seconds since epoch (Jan 1 1970).N/A46
URLValues must be valid urls.N/A8
US Phone Number (ext)US phone format with optional extension. The extension may be separated from the phone number with the following chars/strings: x, X, #, ext, etx.Removes US country codes in the following formats: +1, 1-, 1 , 1.44
US State or TerritoryUS states or territories.Transforms between two-digit codes and full state names.50
UUIDAn id of the form 123e4567-e89b-12d3-a456-426655440000.N/A52
Zip / Postal CodeZIP / postal code for location columns. Both 5 and 9 digit codes are accepted.N/A23

Dates

The following table contains the date formats OneSchema can identify and transform into the desired format. TODO include how our dates autofixer identifies if dates of unknown types need to be swapped based on other data.


Date formatExample
MM-DD-YY01-15-90
M-DD-YY1-15-90
DD-MM-YY15-01-90
DD-M-YY15-1-90
MM/DD/YY01/15/90
M/DD/YY1/15/90
MM.DD.YY01.15.90
M.DD.YY1.15.90
MMM-DD-YYJan-15-90
Month-DD-YYJanuary-15-90
DD-MMM-YY15-Jan-90
YYYY-MM-DD1990-01-15
YYYY-M-DD1990-1-15
MM-DD-YYYY01-15-1990
M-DD-YYYY1-15-1990
MM/DD/YYYY01/15/1990
M/DD/YYYY1/15/1990
DD/MM/YYYY15/01/1990
DD/M/YYYY15/1/1990
YYYY/MM/DD1990/01/15
YYYY/M/DD1990/1/15
DD.MM.YYYY15.01.1990
DD.M.YYYY15.1.1990
YYYY.MM.DD1990.01.15
YYYY.M.DD1990.1.15
Month DD, YYYYJanuary 15, 1990
MMM. DD, YYYYJan. 15, 1990
DD MMM. YYYY15 Jan. 1990
DD. MMM. YYYY15. Jan. 1990
ddd, MMM DD, YYYYMon, Jan 15, 1990

Datetimes

The following table contains the datetime formats OneSchema can identify and transform into the desired format.


Datetime formatExample
Unix timestamp1670874565
ddd MMM DD HH:mm:ss YYYYWed Jan 15 00:34:60 1990
MM-DD-YY HH:mm01-15-90 10:10
MM/DD/YY HH:mm01/15/90 10:10
MM/DD/YYYY HH:mm01/15/1990 10:10
YYYY/MM/DD HH:mm1990/01/15 10:10
MM/DD/YYYY HH:mm01/15/1990 10:10
MM-DD-YYYY HH:mm01-15-1990 10:10
YYYY-MM-DD HH:mm1990-01-15 10:10
MM/DD/YY HH:mm:ss xm01/15/90 10:10:10 am
ISO 86011990-01-15T00:34:60.026490+09:30
ISO 8601 without timezone offset1990-01-15T00:34:60.026490
YYYY-MM-DDTHH:mm+offset1990-01-15T10:10+9:30
YYYY-MM-DDTHH:mm1990-01-15T10:10Z
MM/DD/YYYY HH:mm xm01/15/1990 10:10 pm
DD-MM-YYYY HH:mm15-01-1990 10:10
DD-MM-YYYY HH:mm xm15-01-1990 10:10 am