When writing ruleset extraction rules, in addition to SQL Lite functions, auxiliary functions implemented for the service are available. With helper functions, it is easier to handle, for example, strings in SQL Lite.
List of available helper functions and their operation
-
substring_part. Parameters: (sourceString, separator, index). The function splits the given string sourceString with the given separator string separator. The return value consists of as much data content as the index value indicates. If the string does not contain this element, NULL is returned. A negative index returns the element pointed to by the index calculated from the last results. -1 returns the last element, -2 returns the second to last, and so on.
-
substring_index() https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_substring-index
-
sql_left() https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_left
-
sql_right() https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_right
-
concat() https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_concat
-
if() https://dev.mysql.com/doc/refman/8.0/en/control-flow-functions.html#function_if
-
timestamp() https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_timestamp
-
strftime(format,timestring) Returns the 'timestring' formatted according to the 'format' string provided. For example, strftime('%Y %m %d', 'now', 'localtime') would extract the Year Month and Day for the current date.
-
curdate() Returns the current date according the server's local timezone (Finland). Format YYYY-MM-DD
-
curtime() Returns the current time according to the server's local timezone (Finland). Format H:i:s
-
now() Returns the current date and time according to the server's local timezone (Finland). Format YYYY-MM-DD HH:MM:SS
-
weekday(date/datetime/timestamp) Returns the day of the week as a number based on the provided date or time. 0 (for Monday) through 6 (for Sunday). For example, if you want to filter out Saturdays and Sundays, you can add the following line to the code: AND weekday(salaryrenderingdate) NOT IN (5, 6)
-
ceil(decimal) Round a decimal up to the nearest whole number
-
floor(decimal) Round a decimal down to the nearest whole number
- round(decimal) Round a decimal to a specific amount of digits.