Advanced ETL Processor users regular expressions data, for example, date formats, postcodes, phone numbers etc. It is also possible to write your own Regular expressions
What are the regular expressions?
A regular expression is a string that is used to describe or match a set of strings, according to certain syntax rules. Regular expressions are used by many text editors, utilities, and programming languages to search and manipulate text based on patterns. For example, Perl and Tcl have a powerful regular expression engine built directly into their syntax. Several utilities provided by Unix distributions—including the editor ed and the filter grep—were the first to popularize the concept of regular expressions.
"Regular expression" is often shortened to regex or regexp (singular), or regexes, regexps, or regexen (plural). Some authors distinguish between regular expression and abbreviated forms such as regex, restricting the former to true regular expressions, which describe regular languages, while using the latter for any regular expression-like pattern, including those that describe languages that are not regular. As only some authors observe this distinction, it is not safe to rely upon it. As an example of the syntax, the regular expression \bex can be used to search for all instances of the string "ex" that occur at word boundaries (signified by the \b). Thus in the string, "Texts for experts," \bex matches the "ex" in "experts," but not in "Texts" (because the "ex" occurs inside the word there and not immediately after a word boundary).
Source: wikipedia.
Note:
For more information on regular expressions please visit: www.regularexpression.info
Most of the regular expressions were taken from www.regexlib.com. We are not able to include a full list of contributors because it is too big. We did our best to test and modify if necessary pattern strings however as time goes by standards changes and some of them could become no longer valid. Therefore Regular Expression Validation Rules should be used with caution.
Most of the regular expressions were taken from www.regexlib.com. We are not able to include a full list of contributors because it is too big. We did our best to test and modify if necessary pattern strings however as time goes by standards changes and some of them could become no longer valid. Therefore Regular Expression Validation Rules should be used with caution.
If you have any useful Regular expression please let us know we would be more than happy to include it in the next release of Advanced ETL Processor
Below is a list of Regular expression Transformation Functions supported by Advanced ETL Processor:
| Image | Data Validation Function | Description |
|---|---|---|
| Regular Expression | Validates data against a regular expression | |
| Is Algerian Post Code | Validates Algerian Post Code Format | |
| Is Andorran IBAN | Validates Andorran International Bank Account Number | |
| Is Andorran Post Code | Validates Andorran Post Code format | |
| Is Argentinean Post Code | Validates Argentinean Post Code | |
| Is Australian Mobile Number | Validates Australian Mobile Number | |
| Is Australian Phone Number | Validation of Australian phone number. Accepts all forms of Australian phone numbers in different formats (area code in brackets, no area code, spaces between 2-3 and 6-7th digits, +61 international dialling code). Checks that area codes are valid (when entered). | |
| Is Australian Post Code | Australian postal code verification. Australia has 4-digit numeric postal codes with the following state-based specific ranges. ACT: 0200-0299 and 2600-2639. NSW: 1000-1999, 2000-2599 and 2640-2914. NT: 0900-0999 and 0800-0899. QLD: 9000-9999 and 4000-4999. SA: 5000-5999. TAS: 7800-7999 and 7000-7499. VIC: 8000-8999 and 3000-3999. WA: 6800-6999 and 6000-6799 | |
| Is Austrian Mobile Phone Number | Validates Austrian Mobile Phone Number | |
| Is Azerbaijani Post Code | Validates Azerbaijani Post Code format | |
| Is Bahraini Post Code | Validates Bahraini Post Code format | |
| Is Bangladeshi Post Code | Validates Bangladeshi Post Code format | |
| Is Belarusian Post Code | Validates Belarusian Post Code format | |
| Is Belgian Post Code | Validates Belgian Post Code | |
| Is Belgian VAT Number | Belgian VAT Numbers format verification with support for optional member state definition. | |
| Is Brunei Post Code | Validates Brunei Post Code format | |
| Is Canadian Branch-Transit/ Bank code | Validates Canadian Branch-Transit number. The branch number must be 3 or 4 digits then '-' then five digits | |
| Is Canadian Provincial Code | Validates Canadian provincial codes | |
| Is Canadian Social Insurance Number | Validates Canadian Social Insurance Number | |
| Is Canadian Zip Code | Canadian postal code format verification. The format of a Canadian postal code is LDL DLD where L are alpha characters and D are numeric digits. But there are some exceptions. The letters D, F, I, O, Q and U never appear in a postal code because of their Advanced similarity to 0, E, 1, 0, 0, and V respectively. In addition to avoiding the six "forbidden" letters W and Z also do not appear as the first letter of a postal code (at least not at present). | |
| Is Chilean Post Code | Validates Chilean Post Code format | |
| Is Credit Card | Validation of all major credit cards formats | |
| Is Croatian Post Code | Validates Croatian Post Code format | |
| Is Cyprus Post Code | Validates Cyprus Post Code format | |
| Is Cyprus VAT Number | Cyprus VAT Numbers format verification with support for optional member state definition | |
| Is Czech Republic IBAN | Validates Czech International Bank Account Number | |
| Is Czech Republic VAT Number | Czech Republic VAT Numbers format verification with support for optional member state definition. | |
| Is Danish Civil Registration Number | Validates Danish civil registration number, which consists of 10 numeric characters and one special character '-'. The format is DDMMYY-XXXX | |
| Is Danish Phone Number | Validates Danish Phone Number | |
| Is Danish Post Code | Validates Danish Post Code | |
| Is Danish VAT Number | Validates Danish VAT Numbers format with support for optional member state definition. | |
| Is Dutch Car Number Plate | Validates Dutch Car Number Plate | |
| Is Dutch Phone Number | Validates Dutch Phone Number | |
| Is Dutch Post Code | Validates Dutch Postal Code | |
| Is Egyptian Post Code | Validates Egyptian Post Code format | |
| Is Email | Email Address validation that allows both IP addresses and regular domains. In the case of an IP address, it makes sure that it is no more than 255 for each part. | |
| Is Estonian Post Code | Validates Estonian Post Codes | |
| Is Estonian VAT Number | Estonian VAT Numbers format verification with support for optional member state definition. | |
| Is Ethiopian Post Code | Validates Ethiopian Post Code format | |
| Is Faroe Islands Post Code | Validates Faroe Islands Post Code format | |
| Is File Name | Validation of filename | |
| Is Finnish VAT Number | Finnish VAT Numbers format verification with support for optional member state definition. | |
| Is French Phone Number | French Phone Number Validation | |
| Is French Polynesia Post Code | Validates French Polynesia Post Code format | |
| Is French Post Code | French Post Code Validation | |
| Is French VAT Number | French VAT Numbers format verification with support for optional member state definition. | |
| Is German Car License Plate | German Car License Plate Validation | |
 |
Is German IBAN | German International Bank Account Number Validation |
| Is German Post Code | Validates German Post Code format | |
| Is German VAT Number | German VAT Numbers format verification with support for optional member state definition. | |
| Is Greek VAT Number | Greek VAT Numbers format verification with support for optional member state definition. | |
| Is Guadeloupe Post Code | Validates Guadeloupe Post Code format | |
| Is Guinea-Bissau Post Code | Validates Guinea-Bissau Post Code format | |
| Is Guinean Post Code | Validates Guinean Post Code format | |
| Is Hungarian VAT Number | Hungarian VAT Numbers format verification with support for optional member state definition | |
| Is Indian Mobile Number | Validates Indian Mobile Phone Number | |
| Is Indian Phone Number | Validates Indian Phone Number | |
| Is Indian Post Code | Validates Indian Post Code | |
 |
Is Indian Vehicle Registration Number | Validates Indian Vehicle Registration Number |
| Is IP Address V4 | Validates IP addresses in the dotted quad form | |
| Is IP Address V6 | Validates all IPv6 text representations as defined within RFC 2373 | |
| Is Irish VAT Number | Irish VAT Numbers format verification with support for optional member state definition. | |
| Is ISBN 10 | Validation of 10 digits ISBN. The ISBN number must be preceded by the text "ISBN:" or "ISBN-10:", the colon is optional. | |
| Is ISBN 13 | Validation of new 13 digits ISBN. The ISBN number must be preceded by the text "ISBN:" or "ISBN-13:", the colon is optional. | |
| Is Israeli Mobile Phone Number | Validates Israeli Mobile Phone Number | |
| Is Israeli Phone Number | Validates Israeli Phone Number Format | |
| Is Italian IBAN | Italian International Bank Account Number Validation | |
| Is Italian Mobile Number | Validates Italian mobile phone number with (or without) the International code | |
| Is Italian Phone Number | Validates Italian phone number | |
| Is Italian Post Code | Postcode check for Italy (including possible Vatican/Italy indications) | |
| Is Italian VAT Number | Italian VAT Numbers format verification with support for optional member state definition. | |
| Is Latvian VAT Number | Validates Latvian VAT Number | |
| Is Lithuanian VAT Number | Lithuanian VAT Numbers format verification with support for optional member state definition. | |
 |
Is Luxembourg VAT Number | Luxemburg VAT Numbers format verification with support for optional member state definition |
| Is Maltese VAT Number | Maltese VAT Numbers format verification with support for optional member state definition | |
 |
Is Netherlands VAT Number | The Netherlands VAT Numbers format verification with support for optional member state definition. |
| Is Peru Cellular Number | Validates Peru Cellular Number format | |
| Is Peru Phone Number | Validates Peru Phone Number format | |
| Is Polish IBAN | Validates Polish International Bank Account Number | |
| Is Polish VAT Number | Polish VAT Numbers format verification with support for optional member state definition | |
| Is Portuguese VAT Number | Portuguese VAT Numbers format verification with support for optional member state definition. | |
| Is Russian Phone Number | Validates Russian Phone Number | |
| Is Russian Post Code | Validates Russian Post Code | |
| Is Slovakian IBAN | Validates Slovakian International Bank Account Number | |
| Is Slovakian VAT Number | Slovakian VAT Numbers format verification with support for optional member state definition | |
| Is Slovenian VAT Number | Slovenian VAT Numbers format verification with support for optional member state definition | |
| Is South African Mobile Number | only allows valid cell phones from South African service providers | |
| Is South African Phone Number | Validates South African Phone Number | |
| Is South African VAT Number | Validates South African VAT Number | |
| Is Spanish IBAN | Spanish IBAN Validation | |
| Is Spanish Phone Number | Spanish Phone Number Validation | |
| Is Spanish Post Code | Spanish Post Code Validation | |
| Is Spanish VAT Number | Spanish VAT Numbers format verification with support for optional member state definition. | |
| Is Swedish IBAN | Swedish International Bank Account Number IBAN | |
| Is Swedish Person Number | Validates Swedish Person Number | |
| Is Swedish Phone Number | Validates Swedish Phone Number | |
| Is Swedish Post Code | Validates Swedish Post Code Format | |
| Is Swedish VAT Number | Swedish VAT Numbers format verification with support for optional member state definition. | |
| Is UK Bank Sort Code | Validation of United Kingdom Bank Sort code | |
| Is UK Driver Licence | Validates the United Kingdom Drivers License format as described by the DVLA.See http://www.govtalk.gov.uk/gdsc/html/frames/default.htm. Matches: 1. Must be 16 characters 2. First 5 characters are alphanumeric. 3. Next 6 characters must be numeric 4. Next 3 characters are alphanumeric 5. Last 2 characters are alpha 6. Second character of numeric section can only be 0, 1, 5 or 6. 7. Fourth and fifth characters of the numeric section must be in the range 01 to 31 | |
| Is UK Mobile Number | United Kingdom mobile phone number validation with optional +44 national code, also allows optional brackets and spaces at appropriate positions | |
| Is UK National Insurance Number | Validation of the United Kingdom National Insurance Number | |
| Is UK Phone Number | Validation of United Kingdom phone number also allows +44 national code in addition to extension numbers of 3 or 4 digits | |
| Is UK Post Code | Validates United Kingdom Post Code | |
| Is UK VAT Number | United Kingdom VAT Numbers format verification with support for optional member state definition. | |
| Is UK Vehicle Registration Number | Validation of United Kingdom Vehicle Registration Number | |
| Is UNC | Validation of UNC | |
| Is URL | Validation of URL | |
| Is US or CA Zip code | Validates US or CA Zip code format | |
| Is US Phone Number | US Phone Number Validation | |
| Is US Social Security Number | Validates US Social security number | |
| Is US State | Validates US State 2 letter abbreviations | |
| Is US Zip Code | Validates US Zip Code |
;
Advanced ETL Processor is designed to automate extracting data from ANY database, transform, validate it and load into ANY database. Typical usage of it would be extract data from Excel File, Validate Date Formats, Sort data, deduplicate it and load it into Oracle database, run stored procedure or Sql script, once loading is completed. Unlike Oracle SQL loader, BCP, DTS or SSIS Advanced ETL Processor can also add new and update old records based on the primary key
Direct link, no registration required.
