|
Note: These functions are available in ETL module version 1.1.271 and newer. |
ETL extends capabilities of SQLite engine by including the following, non-standard functions:
•SHA1
Returns a 40-character string representing 160-bit (20 bytes) SHA1 hash of an input element. This function requires a single parameter.
•MD5
Returns a 32-character string representing 128-bit (16 bytes) MD5 hash of an input element. This function requires a single parameter.
•REGEXP
Returns true if the value matches a given expression. This functions requires two parameters.
•CONVERT_TO_DATE
Converts a string to date format. Optionally, a second argument can be used to specify the actual parsing format (.NET convention). If no second parameter is passed, then standard .NET parsing will occur, with extra check for date in format M/dd/yy hh:mm:ss tt. Bear in mind this function is primarily for internal use.
Sample usages:
•[Mail] REGEXP "@raynet\.(de|ch|com)$"
Returns 1 if the value from the Mail column matches the given pattern.
•REGEXP ("@raynet\.(de|ch|com)$", "m.otorowski@raynet.de"
(Alternative syntax) Returns 1 if the value from the Mail column matches the given pattern.
•SHA1([Mail)
Returns 40-byte SHA-1 hash of the value from the Mail column.
Special characters and escaping sequences in Regular Expression patterns
Regular expressions use \ (backslash) as an escape sequence. Bear in mind the same escape sequence is used by JSON schema. This means, that when escaping a regular expression inside a JSON file, it is necessary to perform "double-escaping".
For example, to test whether the name consists of letters, followed by a dot, followed by a number, the following Regular Expression can be used:
^[a-zA-Z]+\.[0-9]+$
Explanation:
•^ means this is the beginning of the string
•$ means the string ends here
•[a-zA-Z]+ - one or more instances of characters from range a-z or A-Z.
•[0-9]+ - one or more digit
•\. - literal dot. Dot has a special meaning in Regular Expressions (any character), and to use it literally it must be escaped with \ (backslash).
However, the backslash in the regular expression must be escaped once more when used in JSON file, so that it is not interpreted as escape JSON sequence. The following JSON would be valid:
"sql": "[columnName] REGEXP '^[a-zA-Z]+\\.[0-9]+$'"