SQL Functions (for ODBC and Data Gateway)
ZappySys API Drivers support many SQL functions along with Placeholder Functions. When you use SQL Function it invokes client side engine which can slow down query execution if you have many records. So only use SQL Functions if not possible using serverside functionality (i.e. using API Parameters). Here is an example of how to use SQL functions.
select Upper('abcd'), Readfile('c:\test.txt')
Content
Introduction
ZappySys API Drivers support many SQL functions along with Placeholder Functions. When you use SQL Function it invokes client side engine which can slow down query execution if you have many records. So only use SQL Functions if not possible using serverside functionality (i.e. using API Parameters). Here is an example of how to use SQL functions.
select Upper('abcd'), Readfile('c:\test.txt')
Function List
Function name | Description |
---|---|
REGEX_REPLACE |
Search for string pattern using Regular expression and returns a new string with replaced value Syntax: REGEX_REPLACE(input_text,search_pattern,replace_with[,is_case_sensitive]) |
REGEX_ISMATCH |
Checks if specified search pattern found in the input string. Returns 1 if pattern found else 0 Syntax: REGEX_ISMATCH(input_text,pattern[,ignore_case]) |
REGEX_SEARCH |
This function search lower case string. Syntax: REGEX_SEARCH(input_text,pattern[,match_index][,group_index][,ignore_case][,throw_error_if_not_found][,multi_match_separator]) |
SLEEP |
Adds delay for specified amount of milliseconds. Syntax: SLEEP(delay_milliseconds) |
JSON_VALUE |
This function returns single value out of JSON document using JSON Path expression. Syntax: JSON_VALUE(json_document,jsonpath_expression[,throw_error_if_nomatch]) |
JSON_ARRAY_FIRST |
This function returns first element from the JSON array. This is same as calling JSON_ARRAY_NTH for 0 index. Syntax: JSON_ARRAY_FIRST(json_array) |
JSON_ARRAY_LAST |
This function returns last element from the JSON array. This is useful when you don't know how many elements there and want to extract last one. Syntax: JSON_ARRAY_LAST(json_array) |
JSON_ARRAY_NTH |
This function returns JSON array element for specified position (0 based index). Syntax: JSON_ARRAY_NTH(json_array,index) |
BASE64_TO_BYTES |
This function returns byte array from specified Base64 string. Syntax: BASE64_TO_BYTES(base64_string) |
BYTES_TO_BASE64 |
This function encodes byte array into base64 string. You can use function like BASE64_TO_BYTES to generate Byte Array. Syntax: BYTES_TO_BASE64(byte_array) |
TEXT_TO_BASE64 |
This function encodes plain text into base64 string. Syntax: TEXT_TO_BASE64(plain_text) |
BASE64_TO_TEXT |
This function decodes base64 string and returns plain text. Syntax: BASE64_TO_TEXT(base64_text) |
FILE_TO_BASE64 |
This function returns base64 string of file content (binary or text file). Path can be local file or URL Syntax: FILE_TO_BASE64(filepath_or_url) |
BASE64_TO_FILE |
This function saves bytes of base64 string to file. You can create Text/Binary file out of Base64 string using this function. Syntax: BASE64_TO_FILE(filepath,base64_content) |
FILE_READ_TEXT |
This function reads text from local file. To read data from binary file use FILE_TO_BASE64 function or FILE_READ_BINARY function Syntax: FILE_READ_TEXT(filepath[,continueOnError]) |
FILE_READ_BINARY |
This function reads data from local file as byte array (i.e. byte[]). To read data as text use FILE_READ_TEXT function. Syntax: FILE_READ_BINARY(filepath[,continueOnError]) |
FILE_WRITE_TEXT |
This function writes text to local file and returns bytes written to the file. If folder used in path is missing then it creates automatically. If file already exists then it overwrites it. Syntax: FILE_WRITE_TEXT(filepath,content[,failIfExists]) |
FILE_WRITE_BINARY |
This function writes byte array to file and returns total bytes written to the file. To generate input bytes array you can use function like BASE64_TO_BYTES and pass as input to this function. Syntax: FILE_WRITE_BINARY(filepath,byte_array[,failIfExists]) |
HASH_TEXT |
This function returns hash of specified input text. You can get hash in HEX or BASE64 format by supplying additional arguments Syntax: HASH_TEXT(algorithm,input_text[,output_format][,uppercase_hex]) |
HASH_FILE |
This function returns hash of specified input file(text or binary). You can get hash in HEX or BASE64 format by supplying additional arguments Syntax: HASH_FILE(algorithm,input_text[,output_format][,uppercase_hex]) |
CONCAT |
This function concat multiple strings. You can also use || operator rather than this function Syntax: CONCAT(string1,string2,string3,stringN) |
LOWER |
This function returns lower case string. Syntax: LOWER(input_text) |
UPPER |
This function returns upper case string. Syntax: UPPER(input_text) |
SUBSTR |
Extract and returns a substring with a predefined length starting at a specified position in a source string. Syntax: SUBSTR(input_text,start_position,length) |
TRIM |
Trim spaces or specified characters from the left side and right side of the input string and returns a new string. Syntax: TRIM(input_text[,characters_to_trim]) |
LTRIM |
Trim spaces or specified characters from the left side of the input string and returns a new string. Syntax: LTRIM(input_text[,characters_to_trim]) |
RTRIM |
Trim spaces or specified characters from the right side of the input string and returns a new string. Syntax: RTRIM(input_text[,characters_to_trim]) |
LENGTH |
Returns total number of characters in the input string. Syntax: LENGTH(input_text) |
REPLACE |
The replace(X,Y,Z) function returns a string formed by substituting string Z for every occurrence of string Y in string X. The BINARY collating sequence is used for comparisons. If Y is an empty string then return X unchanged. If Z is not initially a string, it is cast to a UTF-8 string prior to processing. Syntax: REPLACE(input_text,search_for,replace_with) |
INSTR |
The instr(X,Y) function finds the first occurrence of string Y within string X and returns the number of prior characters plus 1, or 0 if Y is nowhere found within X. Or, if X and Y are both BLOBs, then instr(X,Y) returns one more than the number bytes prior to the first occurrence of Y, or 0 if Y does not occur anywhere within X. If both arguments X and Y to instr(X,Y) are non-NULL and are not BLOBs then both are interpreted as strings. If either X or Y are NULL in instr(X,Y) then the result is NULL. Syntax: INSTR(input_text,search_for) |
CHAR |
Returns character(s) for specified numeric code(s). Syntax: CHAR(charcode1[,charcode2][,charcode3][,charcode4][,charcode5][,charcode6][,charcode7][,charcode8]) |
HEX |
The hex() function interprets its argument as a BLOB and returns a string which is the upper-case hexadecimal rendering of the content of that blob.
If the argument X in hex(X) is an integer or floating point number, then interprets its argument as a BLOB means that the binary number is first converted into a UTF8 text representation, then that text is interpreted as a BLOB.Hence, hex(12345678) renders as 3132333435363738 not the binary representation of the integer value 0000000000BC614E. Syntax: HEX(input_text) |
ABS |
The abs function returns the absolute value of the numeric argument X. Abs(X) returns NULL if X is NULL. Abs(X) returns 0.0 if X is a string or blob that cannot be converted to a numeric value. If X is the integer -9223372036854775808 then abs(X) throws an integer overflow error since there is no equivalent positive 64-bit two complement value. Syntax: ABS(input_number) |
SIGN |
The sign function returns -1, 0, or +1 if the argument is a numeric value that is negative, zero, or positive, respectively. If the argument to sign(X) is NULL or is a string or blob that cannot be losslessly converted into a number, then sign(X) return NULL. Syntax: SIGN(input_number) |
ROUND |
The round(X,Y) function returns a floating-point value X rounded to Y digits to the right of the decimal point. If the Y argument is omitted, it is assumed to be 0. Syntax: ROUND(input_number[,digits]) |
AVG |
returns the average of values Syntax: AVG([DISTINCT | ALL] expression) |
SUM |
returns the sum of values Syntax: SUM([DISTINCT | ALL] expression) |
COUNT |
returns the count of values Syntax: COUNT([DISTINCT | ALL] expression) |
MIN |
returns minimum value Syntax: MIN(expression) |
MAX |
returns maximum value Syntax: MAX(expression) |
GROUP_CONCAT |
returns a string that is the concatenation of all non-NULL values of the input expression separated by the separator Syntax: GROUP_CONCAT([DISTINCT | ALL] expression,separator) |
RANDOM_INT |
Returns random number between specified numbers. If boundary not specified then returns number between -2147483648 and +2147483647. Syntax: RANDOM_INT([start][,end]) |
RANDOM_LONG |
Returns random number between specified numbers. If boundary not specified then returns number between -9223372036854775808 and +9223372036854775807. Syntax: RANDOM_LONG([start][,end]) |
RANDOM_INT |
Returns random decimal number between specified numbers. If boundary not specified then returns number between -79,228,162,514,264,337,593,543,950,335 and +79,228,162,514,264,337,593,543,950,335. Syntax: RANDOM_INT([start][,end]) |
RANDOM_STRING |
Returns random string with specified length. Syntax: RANDOM_STRING([length]) |
RANDOM_IMAGE |
Returns random image (i.e. byte array of image). You can use output of this function as input for functions like file_write_binary or bytes_to_base64 which takes byte[] as input. Syntax: RANDOM_IMAGE() |
DATE |
This function takes a time string as an argument (and optionally one or more modifiers). Returns date in this format: YYYY-MM-DD (i.e. Equivalent strftime('%Y-%m-%d', ...)). Syntax: DATE(timestring[,modifier1][,modifier2][,modifier3][,modifier4][,modifier5][,modifier6][,modifier7][,modifier8]) |
DATETIME |
This function takes a time string as an argument (and optionally one or more modifiers). Returns date in this format: YYYY-MM-DD HH:MM:SS (i.e. Equivalent strftime('%Y-%m-%d %H:%M:%S', ...)). Syntax: DATETIME(timestring[,modifier1][,modifier2][,modifier3][,modifier4][,modifier5][,modifier6][,modifier7][,modifier8]) |
TIME |
This function takes a time string as an argument (and optionally one or more modifiers). Returns date in this format: HH:MM:SS (i.e. Equivalent strftime('%H:%M:%S', ...)). Syntax: TIME(timestring[,modifier1][,modifier2][,modifier3][,modifier4][,modifier5][,modifier6][,modifier7][,modifier8]) |
JULIANDAY |
This function takes a time string as an argument (and optionally one or more modifiers). Returns date in this format: DDDDDDD.DDDDDDD (i.e. Equivalent strftime('%J', ...)). Syntax: JULIANDAY(timestring[,modifier1][,modifier2][,modifier3][,modifier4][,modifier5][,modifier6][,modifier7][,modifier8]) |
STRFTIME |
This function takes a time string as an argument (and optionally one or more modifiers). Returns date in this format: (specified by format parameter). Syntax: STRFTIME(format,timestring[,modifier1][,modifier2][,modifier3][,modifier4][,modifier5][,modifier6][,modifier7][,modifier8]) |
NULLIF |
The nullif(X,Y) function returns its first argument if the arguments are different and NULL if the arguments are the same. The nullif(X,Y) function searches its arguments from left to right for an argument that defines a collating function and uses that collating function for all string comparisons. If neither argument to nullif() defines a collating function then the BINARY is used. Syntax: NULLIF(expr1,expr2) |
IFNULL |
The ifnull() function returns a copy of its first non-NULL argument, or NULL if both arguments are NULL. Ifnull() must have exactly 2 arguments. The ifnull() function is equivalent to coalesce() with two arguments. Syntax: IFNULL(expr1,expr2) |
COALESCE |
The coalesce() function returns a copy of its first non-NULL argument, or NULL if all arguments are NULL. Coalesce() must have at least 2 arguments. Syntax: COALESCE(expr1,expr2[,expr3][,expr4][,expr5][,expr6][,expr7][,expr8]) |
SOUNDEX |
The soundex(X) function returns a string that is the soundex encoding of the string X. The string '?000' is returned if the argument is NULL or contains no ASCII alphabetic characters. Syntax: SOUNDEX(input_text) |
String Functions
REGEX_REPLACE
Syntax:REGEX_REPLACE(input_text,search_pattern,replace_with[,is_case_sensitive])
Parameters:
Name | Description |
---|---|
input_text | [String] The string to be replaced. |
search_pattern | [String] Regular expression pattern you like to search for. |
replace_with | [String] New string value you like to replace. |
is_case_sensitive (Optional) | [Boolean] Specifies whether search pattern is case sensitive or not. [true] means Case-Sensitive search (Default=false) |
Examples:
Example | Sample Output | Description |
---|---|---|
| I like Hat0-red and Hat1-blue | You can use $1, $2...as Group indicator to extract sub values from pattern search. $1 represents first group (\d+) for cap number and $2 represents (\w+) for color |
| I like Hat0-red and Hat1-blue | Example of Case-Insensitive search. See last parameter is 1 rather than default 0 |
REGEX_ISMATCH
Syntax:REGEX_ISMATCH(input_text,pattern[,ignore_case])
Parameters:
Name | Description |
---|---|
input_text | [String] Input string. |
pattern | [String] Regular expression pattern you like to search for. |
ignore_case (Optional) | [Boolean] Specifies if search is case-sensitive or not. Default=false (Case-Sensitive search by default) |
Examples:
Example | Sample Output | Description |
---|---|---|
| 1 | Search for number anywhere in string |
| 1 | Check if string starting with numeric value |
| 1 | Check if string ending with numeric value |
| 1 | Check if string is integer value |
| 0 | Check if string is integer value |
| 0 | Search for string |
| 1 | Search for string (case-insensitive mode) |
REGEX_SEARCH
Syntax:REGEX_SEARCH(input_text,pattern[,match_index][,group_index][,ignore_case][,throw_error_if_not_found][,multi_match_separator])
Parameters:
Name | Description |
---|---|
input_text | [String] Input string. |
pattern | [String] Regular expression pattern you like to search for. |
match_index (Optional) | [String] If multiple match found specify which match you like to output. You can use positive number as index (i.e. 0, 1, 2, 3...) or use negative number to get reverse index (i.e. -0, -1, -2...) or use * to fetch all matches. Default=0 |
group_index (Optional) | [Int32] Which part of match you like to output (if not supplied full match is returned). Default=0 (full match) |
ignore_case (Optional) | [Boolean] Set this to true if you like to perform case-insensitive search. Default=false |
throw_error_if_not_found (Optional) | [Boolean] Set this to true if you like to trow error if match not found. |
multi_match_separator (Optional) | [String] Separator string to use to join multiple matches. Default=new line. |
Examples:
Example | Sample Output | Description |
---|---|---|
| 00AA | Search for alpha-numeric. Return first match if no match index specified (Default=0 i.e. first)) |
| 03DD | Return last match |
| 03 | Return first match and extract 1st group of pattern (i.e. \d+) |
| DD | Return first match and extract 2nd group of pattern (i.e. \w+) |
| 00AA11BB22CC33DD | Return all matches and join without any separator |
| 00AA-11BB-22CC-33DD | Return all matches and join them by dash (-) |
| AA-BB-CC-DD | Return all matches and join them by dash (-) |
| AABBCCDD | Return 2nd group of all matches and join string together |
BASE64_TO_BYTES
Syntax:BASE64_TO_BYTES(base64_string)
Parameters:
Name | Description |
---|---|
base64_string | [String] Input base64 string which you want to convert to byte array. |
Examples:
Example | Sample Output | Description |
---|---|---|
| Bytes[] for Abcd |
CONCAT
Syntax:CONCAT(string1,string2,string3,stringN)
Parameters:
Name | Description |
---|---|
string1 | [String] First string |
string2 | [String] Second string |
string3 | [String] Third string |
stringN | [String] Third string |
Examples:
Example | Sample Output | Description |
---|---|---|
| AABBCC | Concat multiple strings |
| (NULL) | yields null when one or more values are null |
| AABBCCDDEEFF | Concat many strings using function |
| AABBCCDDEEFF | Concat many strings using || operator |
| (NULL) | Concat many strings using || operator yields null when one or more values are null |
LOWER
Syntax:LOWER(input_text)
Parameters:
Name | Description |
---|---|
input_text | [String] Input string which you want to change to lower case. |
Examples:
Example | Sample Output | Description |
---|---|---|
| abcd |
UPPER
Syntax:UPPER(input_text)
Parameters:
Name | Description |
---|---|
input_text | [String] Input string which you want to change to upper case. |
Examples:
Example | Sample Output | Description |
---|---|---|
| ABCD |
SUBSTR
Syntax:SUBSTR(input_text,start_position,length)
Parameters:
Name | Description |
---|---|
input_text | [String] Input string |
start_position | [String] Start position from where you like to start extract. If this number is negative then it starts from the end (reverse oder) |
length | [String] |
Examples:
Example | Sample Output | Description |
---|---|---|
| ZappySys | |
| ZappySys | |
| PowerPack | Reverse start position (-ve number) |
TRIM
Syntax:TRIM(input_text[,characters_to_trim])
Parameters:
Name | Description |
---|---|
input_text | [String] Input text which you like to trim. |
characters_to_trim (Optional) | [String] Characters you like to trim. To use special characters by their decimal number use char(9) || char(10) || char(11) so on. 9=Tab, 32=Space, 11=CR, 10=LF |
Examples:
Example | Sample Output | Description |
---|---|---|
| *** xxABCxx *** | Trims space around input string |
| ABC | Trims space, tab, x or * characters around input string |
| ABC | Trims x,*,space, tab (i.e. char(9)) or new line (i.e. CR LF=char(13), char(10)) characters around input string |
| (NULL) |
LTRIM
Syntax:LTRIM(input_text[,characters_to_trim])
Parameters:
Name | Description |
---|---|
input_text | [String] Input text which you like to trim. |
characters_to_trim (Optional) | [String] Characters you like to trim. To use special characters by their decimal number use char(9) || char(10) || char(11) so on. 9=Tab, 32=Space, 11=CR, 10=LF |
Examples:
Example | Sample Output | Description |
---|---|---|
| *** xxABCxx *** | Trims space from the left side of the input string |
| ABCxx *** | Trims space, tab, x or * characters from the left side of the input string |
| ABCxx *** | Trims x,*,space, tab (i.e. char(9)) or new line (i.e. CR LF=char(13), char(10)) characters from the left side of the input string |
| (NULL) |
RTRIM
Syntax:RTRIM(input_text[,characters_to_trim])
Parameters:
Name | Description |
---|---|
input_text | [String] Input text which you like to trim. |
characters_to_trim (Optional) | [String] Characters you like to trim. To use special characters by their decimal number use char(9) || char(10) || char(11) so on. 9=Tab, 32=Space, 11=CR, 10=LF |
Examples:
Example | Sample Output | Description |
---|---|---|
| *** xxABCxx *** | Trims space from the right side of the input string |
| *** xxABC | Trims space, tab, x or * characters from the right side of the input string |
| xxABC | Trims x,*,space, tab (i.e. char(9)) or new line (i.e. CR LF=char(13), char(10)) characters from the right side of the input string |
| (NULL) |
LENGTH
Syntax:LENGTH(input_text)
Parameters:
Name | Description |
---|---|
input_text | [String] Input text for which you like calculate length. |
Examples:
Example | Sample Output | Description |
---|---|---|
| 3 | |
| 5 | |
| (NULL) |
REPLACE
Syntax:REPLACE(input_text,search_for,replace_with)
Parameters:
Name | Description |
---|---|
input_text | [String] Input text for which you like search and replace. |
search_for | [String] Text you like to search in input text. |
replace_with | [String] New text you like to replace with if search_text found. |
Examples:
Example | Sample Output | Description |
---|---|---|
| The sky is blue | Search for word 'green' and replace with word 'blue' |
| (NULL) | Any null input results in null |
INSTR
Syntax:INSTR(input_text,search_for)
Parameters:
Name | Description |
---|---|
input_text | [String] Input text for which you like search and replace. |
search_for | [String] Text you like to search in input text. |
Examples:
Example | Sample Output | Description |
---|---|---|
| 1 | Returns position of first occurrence of word 'The' |
| 9 | Returns position of first occurrence of word 'is' |
| 0 | Returns 0 if word not found |
CHAR
Syntax:CHAR(charcode1[,charcode2][,charcode3][,charcode4][,charcode5][,charcode6][,charcode7][,charcode8])
Parameters:
Name | Description |
---|---|
charcode1 | [Int32] Integer number which represents the 1st character. |
charcode2 (Optional) | [Int32] Integer number which represents the 2nd character. |
charcode3 (Optional) | [Int32] Integer number which represents the 3rd character. |
charcode4 (Optional) | [Int32] Integer number which represents the 4th character. |
charcode5 (Optional) | [Int32] Integer number which represents the 5th character. |
charcode6 (Optional) | [Int32] Integer number which represents the 6th character. |
charcode7 (Optional) | [Int32] Integer number which represents the 7th character. |
charcode8 (Optional) | [Int32] Integer number which represents the 8th character. |
Examples:
Example | Sample Output | Description |
---|---|---|
| A | Returns Ascii char A |
| B | Returns Ascii char B |
| न | Returns Unicode character न |
| ABC | Returns string from multiple ascii character codes |
| नयन | Returns string from multiple unicode character codes |
| {newline} | Returns New line char which is made from 2 chars CR+LF (i.e. char(13) + char(10)) |
| {tab} | Returns Tab character |
| (NULL) |
HEX
Syntax:HEX(input_text)
If the argument X in hex(X) is an integer or floating point number, then interprets its argument as a BLOB means that the binary number is first converted into a UTF8 text representation, then that text is interpreted as a BLOB.Hence, hex(12345678) renders as 3132333435363738 not the binary representation of the integer value 0000000000BC614E.
Parameters:
Name | Description |
---|---|
input_text | [String] Input text for which you like generate HEX string. |
Examples:
Example | Sample Output | Description |
---|---|---|
| 41 | Hex value of char A |
| 2A | Hex value of char * |
| 4142432A | Hex value of 4 chars A,B,C and * |
| (NULL) |
RANDOM_STRING
Syntax:RANDOM_STRING([length])
Parameters:
Name | Description |
---|---|
length (Optional) | [String] range start number for random number generation. |
Examples:
Example | Sample Output | Description |
---|---|---|
| AbCd@!4124Xyz@%)*123 | Returns random string of 20 characters by default |
| Abc | Returns random string of 3 characters |
RANDOM_IMAGE
Syntax:RANDOM_IMAGE()
Examples:
Example | Sample Output | Description |
---|---|---|
| byte[...] | Byte array of some random image which can be saved to disk by calling |
| iVBORw0KG....{many more}....rkJggg== | Base64 of byte[] returned from RANDOM_IMAGE function |
SOUNDEX
Syntax:SOUNDEX(input_text)
Parameters:
Name | Description |
---|---|
input_text | [String] Input text for which you like return soundex encoded string. |
Examples:
Example | Sample Output | Description |
---|---|---|
| N620 | |
| N620 | |
| C220 | |
| C220 | |
| C220 | |
| C220 | |
| ?000 | For null input returns ?000 |
Other Functions
SLEEP
Syntax:SLEEP(delay_milliseconds)
Parameters:
Name | Description |
---|---|
delay_milliseconds | [Int32] Specifies how many milliseconds delay. |
Examples:
Example | Sample Output | Description |
---|---|---|
| 1 | Sleeps for 2 seconds. Returns 1 if successful |
JSON Functions
JSON_VALUE
Syntax:JSON_VALUE(json_document,jsonpath_expression[,throw_error_if_nomatch])
Parameters:
Name | Description |
---|---|
json_document | [String] JSON document from which you like to extract a single value. |
jsonpath_expression | [String] Filter expression (JSONPath) to extract single value from the input JSON document. Learn more about JSON Path here: https://zappysys.com/links/?id=jpath_help |
throw_error_if_nomatch (Optional) | [Boolean] Throw error if match not found for supplied JSON Path expression. |
Examples:
Example | Sample Output | Description |
---|---|---|
| USA | Returns country from location. |
| INDIA | Returns country of second item from locationList array. |
| INDIA | Returns country of item where city=='Mumbai'. |
JSON_ARRAY_FIRST
Syntax:JSON_ARRAY_FIRST(json_array)
Parameters:
Name | Description |
---|---|
json_array | [String] JSON array string from which you like to extract a single value. |
Examples:
Example | Sample Output | Description |
---|---|---|
| Jan | Returns 1st element from JSON array |
JSON_ARRAY_LAST
Syntax:JSON_ARRAY_LAST(json_array)
Parameters:
Name | Description |
---|---|
json_array | [String] JSON array string from which you like to extract a single value. |
Examples:
Example | Sample Output | Description |
---|---|---|
| Mar | Returns 3rd element from JSON array |
JSON_ARRAY_NTH
Syntax:JSON_ARRAY_NTH(json_array,index)
Parameters:
Name | Description |
---|---|
json_array | [String] JSON array string from which you like to extract a single value. |
index | [String] Array element index (0 based) |
Examples:
Example | Sample Output | Description |
---|---|---|
| Jan | Returns 1st element from JSON array |
| Mar | Returns 3rd element from JSON array |
Encoding Functions
BYTES_TO_BASE64
Syntax:BYTES_TO_BASE64(byte_array)
Parameters:
Name | Description |
---|---|
byte_array | [Byte] Input string which you like to encode in base64. |
Examples:
Example | Sample Output | Description |
---|---|---|
| QWJjZA== | Returns Base64 string from byte array. |
TEXT_TO_BASE64
Syntax:TEXT_TO_BASE64(plain_text)
Parameters:
Name | Description |
---|---|
plain_text | [String] Input string which you like to encode in base64. |
Examples:
Example | Sample Output | Description |
---|---|---|
| QWJjZA== | Encodes plain text into Base64 string |
BASE64_TO_TEXT
Syntax:BASE64_TO_TEXT(base64_text)
Parameters:
Name | Description |
---|---|
base64_text | [String] Input string encoded in base64. |
Examples:
Example | Sample Output | Description |
---|---|---|
| Abcd | Decodes Base64 text into plain text |
FILE_TO_BASE64
Syntax:FILE_TO_BASE64(filepath_or_url)
Parameters:
Name | Description |
---|---|
filepath_or_url | [String] File Path or URL for which you want to get Base64 encoded string. If you specify URL for File then HTTP request is made to download data before it converts to Base64 string. |
Examples:
Example | Sample Output | Description |
---|---|---|
|
| Returns Base64 encoded text of file content (can be text file or binary data like png, zip, mp3) |
|
| Returns Base64 encoded text of file from URL |
BASE64_TO_FILE
Syntax:BASE64_TO_FILE(filepath,base64_content)
Parameters:
Name | Description |
---|---|
filepath | [String] File path where you like to save content. |
base64_content | [String] Base64 string which you like decode as byte array and save to local file (binary/text data). |
Examples:
Example | Description |
---|---|
| Returns Base64 encoded text of file content (can be text file or binary data like png, zip, mp3) |
File Functions
FILE_READ_TEXT
Syntax:FILE_READ_TEXT(filepath[,continueOnError])
Parameters:
Name | Description |
---|---|
filepath | [String] File path from where you like to read data. |
continueOnError (Optional) | [Boolean] Continue if file not found (Returns null rather than error when this option is set). |
Examples:
Example | Sample Output | Description |
---|---|---|
| abcd | Returns text from a specified file (assuming file content is abcd) |
| --ERROR-- | Pass bad path |
| (NULL) | (Set continueOnError=true | 1) Returns null rather than error if file not found |
FILE_READ_BINARY
Syntax:FILE_READ_BINARY(filepath[,continueOnError])
Parameters:
Name | Description |
---|---|
filepath | [String] File path from where you like to read data. |
continueOnError (Optional) | [Boolean] Continue if file not found (Returns null rather than error when this option is set). |
Examples:
Example | Sample Output | Description |
---|---|---|
| abcd | Returns text from a specified file (assuming file content is abcd) |
| --ERROR-- | Pass bad path |
| (NULL) | (Set continueOnError=true | 1) Returns null rather than error if file not found |
FILE_WRITE_TEXT
Syntax:FILE_WRITE_TEXT(filepath,content[,failIfExists])
Parameters:
Name | Description |
---|---|
filepath | [String] File path where you like to create new file. |
content | [String] String which you want to write to the file. |
failIfExists (Optional) | [Boolean] Fail if file already exists. Default=0 (i.e. overwrite file). |
Examples:
Example | Sample Output | Description |
---|---|---|
| 4 | Returns length of text written to the file |
FILE_WRITE_BINARY
Syntax:FILE_WRITE_BINARY(filepath,byte_array[,failIfExists])
Parameters:
Name | Description |
---|---|
filepath | [String] File path where you like to create new file. |
byte_array | [String] Bytes you like to save to file. |
failIfExists (Optional) | [Boolean] Fail if file already exists. Default=0 (i.e. overwrite file). |
Examples:
Example | Sample Output | Description |
---|---|---|
| 4 | Saves plain text 'Abcd' to file and returns bytes count written to file. |
HASH Functions
HASH_TEXT
Syntax:HASH_TEXT(algorithm,input_text[,output_format][,uppercase_hex])
Parameters:
Name | Description |
---|---|
algorithm | [String] Hash algorithm you like to use. Possible options are md5,sha1,sha256,sha512 |
input_text | [String] Input text for which you like to produce hash |
output_format (Optional) | [String] Output format. Possible options are 'hex' or 'base64'. Default=hex |
uppercase_hex (Optional) | [Boolean] If output_format is 'hex' then you can supply this parameter as 1 to produce Upper case string. Default=0 (i.e. lower case hex) |
Examples:
Example | Sample Output | Description |
---|---|---|
| e99a18c428cb38d5f260853678922e03 | Calculate MD5 Hash - Output in Hex format - lower case (default parameters omitted - Output_format=Hex, uppercase_hex=0) |
| e99a18c428cb38d5f260853678922e03 | Calculate MD5 Hash - Output in Hex format - lower case |
| E99A18C428CB38D5F260853678922E03 | Calculate MD5 Hash - Output in Hex format - upper case |
| 6ZoYxCjLONXyYIU2eJIuAw== | Calculate MD5 Hash - Output in Base64 format |
| 6ca13d52ca70c883e0f0bb101e425a89e8624de51db2d2392593af6a84118090 | Calculate SHA256 Hash - Output in Hex format - lower case |
| bKE9UspwyIPg8LsQHkJaiehiTeUdstI5JZOvaoQRgJA= | Calculate SHA256 Hash - Output in Base64 format |
HASH_FILE
Syntax:HASH_FILE(algorithm,input_text[,output_format][,uppercase_hex])
Parameters:
Name | Description |
---|---|
algorithm | [String] Hash algorithm you like to use. Possible options are md5,sha1,sha256,sha512 |
input_text | [String] Input file for which you like to produce hash |
output_format (Optional) | [String] Output format. Possible options are 'hex' or 'base64'. Default=hex |
uppercase_hex (Optional) | [Boolean] If output_format is 'hex' then you can supply this parameter as 1 to produce Upper case string. Default=0 (i.e. lower case hex) |
Examples:
Example | Sample Output | Description |
---|---|---|
| e99a18c428cb38d5f260853678922e03 | (input file text: abc123) Calculate MD5 Hash - Output in Hex format - lower case (default parameters omitted - Output_format=Hex, uppercase_hex=0) |
| e99a18c428cb38d5f260853678922e03 | (input file text: abc123) Calculate MD5 Hash - Output in Hex format - lower case |
| E99A18C428CB38D5F260853678922E03 | (input file text: abc123) Calculate MD5 Hash - Output in Hex format - upper case |
| 6ZoYxCjLONXyYIU2eJIuAw== | (input file text: abc123) Calculate MD5 Hash - Output in Base64 format |
| 6ca13d52ca70c883e0f0bb101e425a89e8624de51db2d2392593af6a84118090 | (input file text: abc123) Calculate SHA256 Hash - Output in Hex format - lower case |
| bKE9UspwyIPg8LsQHkJaiehiTeUdstI5JZOvaoQRgJA= | (input file text: abc123) Calculate SHA256 Hash - Output in Base64 format |
Number Functions
ABS
Syntax:ABS(input_number)
Parameters:
Name | Description |
---|---|
input_number | [String] input number for which you like to get absolute value. |
Examples:
Example | Sample Output | Description |
---|---|---|
| 4 | |
| 4 | |
| (NULL) | Null input returns null |
SIGN
Syntax:SIGN(input_number)
Parameters:
Name | Description |
---|---|
input_number | [String] input number for which you like to get sign value. |
Examples:
Example | Sample Output | Description |
---|---|---|
| -1 | |
| 1 | |
| 1 | |
| 0 | |
| (NULL) | Null input returns null |
ROUND
Syntax:ROUND(input_number[,digits])
Parameters:
Name | Description |
---|---|
input_number | [String] Input value which you like to round. |
digits (Optional) | [Int32] digits to the right of the decimal point you like to keep after rounding. |
Examples:
Example | Sample Output | Description |
---|---|---|
| -123234 | Can be any random number between -9223372036854775808 and +9223372036854775807 |
| 123234 | Can be any random number between 0 and +9223372036854775807 |
| 5 | Can be any random number between 0 and +10 |
| 5 | Can be any random number between -10 and +10 |
RANDOM_INT
Syntax:RANDOM_INT([start][,end])
Parameters:
Name | Description |
---|---|
start (Optional) | [String] range start number for random number generation. |
end (Optional) | [String] range end number for random number generation. |
Examples:
Example | Sample Output | Description |
---|---|---|
| -123234 | Can be any random number between -2147483648 and +2147483647 |
| 123234 | Can be any random number between 0 and +2147483647 |
| 5 | Can be any random number between 0 and +10 |
| 5 | Can be any random number between -10 and +10 |
RANDOM_LONG
Syntax:RANDOM_LONG([start][,end])
Parameters:
Name | Description |
---|---|
start (Optional) | [String] range start number for random number generation. |
end (Optional) | [String] range end number for random number generation. |
Examples:
Example | Sample Output | Description |
---|---|---|
| -123234 | Can be any random number between -9223372036854775808 and +9223372036854775807 |
| 123234 | Can be any random number between 0 and +9223372036854775807 |
| 5 | Can be any random number between 0 and +10 |
| 5 | Can be any random number between -10 and +10 |
RANDOM_INT
Syntax:RANDOM_INT([start][,end])
Parameters:
Name | Description |
---|---|
start (Optional) | [String] range start number for random number generation. |
end (Optional) | [String] range end number for random number generation. |
Examples:
Example | Sample Output | Description |
---|---|---|
| -123234 | Can be any random number between -79,228,162,514,264,337,593,543,950,335 and +79,228,162,514,264,337,593,543,950,335 |
| 123234 | Can be any random number between 0 and +79,228,162,514,264,337,593,543,950,335 |
| 5 | Can be any random number between 0 and +10 |
| 5 | Can be any random number between -10 and +10 |
Aggregate Functions
AVG
Syntax:AVG([DISTINCT | ALL] expression)
Parameters:
Name | Description |
---|---|
[DISTINCT | ALL] expression | [String] is a column or an expression that will be used for average. Optionally you can use DISTINCT keyword before expression to remove duplicate values from aggregation. |
Examples:
Example | Sample Output | Description |
---|---|---|
| 5000 | Returns average of Amount column |
| 4000 | Returns average of Amount column and only takes distinct values from Amount column |
SUM
Syntax:SUM([DISTINCT | ALL] expression)
Parameters:
Name | Description |
---|---|
[DISTINCT | ALL] expression | [String] is a column or an expression that will be used for sum. Optionally you can use DISTINCT keyword before expression to remove duplicate values from aggregation. |
Examples:
Example | Sample Output | Description |
---|---|---|
| 5000 | Returns sum of Amount column |
| 4000 | Returns sum of Amount column and only takes distinct values from Amount column |
COUNT
Syntax:COUNT([DISTINCT | ALL] expression)
Parameters:
Name | Description |
---|---|
[DISTINCT | ALL] expression | [String] is a column or an expression that will be used for count. Optionally you can use DISTINCT keyword before expression to remove duplicate values from aggregation. |
Examples:
Example | Sample Output | Description |
---|---|---|
| 5000 | Returns count of all rows (including null and duplicate) |
| 4000 | Returns count of all rows (exclude duplicate rows) |
| 3000 | Returns count of all rows (exclude duplicate rows and null values of OrderId column) |
MIN
Syntax:MIN(expression)
Parameters:
Name | Description |
---|---|
expression | [String] is a column or an expression that will be used to find minimum value. Optionally you can use DISTINCT keyword before expression to remove duplicate values from aggregation. |
Examples:
Example | Sample Output | Description |
---|---|---|
| 3000 | Returns minimum value for Amount column |
MAX
Syntax:MAX(expression)
Parameters:
Name | Description |
---|---|
expression | [String] is a column or an expression that will be used to find maximum value. Optionally you can use DISTINCT keyword before expression to remove duplicate values from aggregation. |
Examples:
Example | Sample Output | Description |
---|---|---|
| 5000 | Returns maximum value for Amount column |
GROUP_CONCAT
Syntax:GROUP_CONCAT([DISTINCT | ALL] expression,separator)
Parameters:
Name | Description |
---|---|
[DISTINCT | ALL] expression | [String] is a column or an expression that will be used for concatenation. Optionally you can use DISTINCT keyword before expression to remove duplicate values from aggregation. |
separator | [String] all values will be separated by the separator. If you skip the separator, the GROUP_CONCAT() function uses a comma (",") by default |
Examples:
Example | Sample Output | Description |
---|---|---|
| US,India,UK,US | Joins country column from all records from Customers table (Use default separator comma) |
| US,India,UK | Joins country column from all records from Customers table and only takes distinct values (Use default separator comma) |
| US;India;UK | Joins country column from all records from Customers table |
Date/Time Functions
DATE
Syntax:DATE(timestring[,modifier1][,modifier2][,modifier3][,modifier4][,modifier5][,modifier6][,modifier7][,modifier8])
Parameters:
Name | Description |
---|---|
timestring | [String] Valid time string format. A time value can be in any of the following formats shown below. The value is usually a string, though it can be an integer or floating point number in the case of format 12. 1. YYYY-MM-DD 2. YYYY-MM-DD HH:MM 3. YYYY-MM-DD HH:MM:SS 4. YYYY-MM-DD HH:MM:SS.SSS 5. YYYY-MM-DDTHH:MM 6. YYYY-MM-DDTHH:MM:SS 7. YYYY-MM-DDTHH:MM:SS.SSS 8. HH:MM 9. HH:MM:SS 10. HH:MM:SS.SSS 11. now 12. DDDDDDDDDD In formats 5 through 7, the 'T' is a literal character separating the date and the time, as required by ISO-8601. Formats 8 through 10 that specify only a time assume a date of 2000-01-01. Format 11, the string 'now', is converted into the current date and time as obtained from the xCurrentTime method of the sqlite3_vfs object in use. The 'now' argument to date and time functions always returns exactly the same value for multiple invocations within the same sqlite3_step() call. Universal Coordinated Time (UTC) is used. Format 12 is the Julian day number expressed as an integer or floating point value. Formats 2 through 10 may be optionally followed by a timezone indicator of the form '[+-]HH:MM' or just 'Z'.The date and time functions use UTC or 'zulu' time internally, and so the 'Z' suffix is a no - op.Any non - zero 'HH:MM' suffix is subtracted from the indicated date and time in order to compute zulu time.For example, all of the following time values are equivalent: 2013 - 10 - 07 08:23:19.120 2013 - 10 - 07T08: 23:19.120Z 2013 - 10 - 07 04:23:19.120 - 04:00 2456572.84952685 In formats 4, 7, and 10, the fractional seconds value SS.SSS can have one or more digits following the decimal point.Exactly three digits are shown in the examples because only the first three digits are significant to the result, but the input string can have fewer or more than three digits and the date / time functions will still operate correctly.Similarly, format 12 is shown with 10 significant digits, but the date / time functions will really accept as many or as few digits as are necessary to represent the Julian day number. |
modifier1 (Optional) | [String] One or more modifier. The time value can be followed by zero or more modifiers that alter date and/or time. Each modifier is a transformation that is applied to the time value to its left. Modifiers are applied from left to right; order is important. The available modifiers are as follows. 1. NNN days 2. NNN hours 3. NNN minutes 4. NNN.NNNN seconds 5. NNN months 6. NNN years 7. start of month 8. start of year 9. start of day 10. weekday N 11. unixepoch 12. localtime 13. utc The first six modifiers (1 through 6) simply add the specified amount of time to the date and time specified by the arguments to the left. The 's' character at the end of the modifier names is optional. Note that '±NNN months' works by rendering the original date into the YYYY-MM-DD format, adding the ±NNN to the MM month value, then normalizing the result. Thus, for example, the data 2001-03-31 modified by '+1 month' initially yields 2001-04-31, but April only has 30 days so the date is normalized to 2001-05-01. A similar effect occurs when the original date is February 29 of a leapyear and the modifier is ±N years where N is not a multiple of four. The 'start of' modifiers (7 through 9) shift the date backwards to the beginning of the current month, year or day. The 'weekday' modifier advances the date forward, if necessary, to the next date where the weekday number is N. Sunday is 0, Monday is 1, and so forth. If the date is already on the desired weekday, the 'weekday' modifier leaves the date unchanged. The 'unixepoch' modifier (11) only works if it immediately follows a time value in the DDDDDDDDDD format. This modifier causes the DDDDDDDDDD to be interpreted not as a Julian day number as it normally would be, but as Unix Time - the number of seconds since 1970. If the 'unixepoch' modifier does not follow a time value of the form DDDDDDDDDD which expresses the number of seconds since 1970 or if other modifiers separate the 'unixepoch' modifier from prior DDDDDDDDDD then the behavior is undefined. For SQLite versions before 3.16.0 (2017-01-02), the 'unixepoch' modifier only works for dates between 0000-01-01 00:00:00 and 5352-11-01 10:52:47 (unix times of -62167219200 through 106751991167). The 'localtime' modifier (12) assumes the time value to its left is in Universal Coordinated Time (UTC) and adjusts that time value so that it is in localtime. If 'localtime' follows a time that is not UTC, then the behavior is undefined. The 'utc' modifier is the opposite of 'localtime'. 'utc' assumes that the time value to its left is in the local timezone and adjusts that time value to be in UTC. If the time to the left is not in localtime, then the result of 'utc' is undefined. |
modifier2 (Optional) | [String] same as above |
modifier3 (Optional) | [String] same as above |
modifier4 (Optional) | [String] same as above |
modifier5 (Optional) | [String] same as above |
modifier6 (Optional) | [String] same as above |
modifier7 (Optional) | [String] same as above |
modifier8 (Optional) | [String] same as above |
Examples:
Example | Sample Output | Description |
---|---|---|
| --see SQL comment for output-- |
DATETIME
Syntax:DATETIME(timestring[,modifier1][,modifier2][,modifier3][,modifier4][,modifier5][,modifier6][,modifier7][,modifier8])
Parameters:
Name | Description |
---|---|
timestring | [String] Valid time string format. A time value can be in any of the following formats shown below. The value is usually a string, though it can be an integer or floating point number in the case of format 12. 1. YYYY-MM-DD 2. YYYY-MM-DD HH:MM 3. YYYY-MM-DD HH:MM:SS 4. YYYY-MM-DD HH:MM:SS.SSS 5. YYYY-MM-DDTHH:MM 6. YYYY-MM-DDTHH:MM:SS 7. YYYY-MM-DDTHH:MM:SS.SSS 8. HH:MM 9. HH:MM:SS 10. HH:MM:SS.SSS 11. now 12. DDDDDDDDDD In formats 5 through 7, the 'T' is a literal character separating the date and the time, as required by ISO-8601. Formats 8 through 10 that specify only a time assume a date of 2000-01-01. Format 11, the string 'now', is converted into the current date and time as obtained from the xCurrentTime method of the sqlite3_vfs object in use. The 'now' argument to date and time functions always returns exactly the same value for multiple invocations within the same sqlite3_step() call. Universal Coordinated Time (UTC) is used. Format 12 is the Julian day number expressed as an integer or floating point value. Formats 2 through 10 may be optionally followed by a timezone indicator of the form '[+-]HH:MM' or just 'Z'.The date and time functions use UTC or 'zulu' time internally, and so the 'Z' suffix is a no - op.Any non - zero 'HH:MM' suffix is subtracted from the indicated date and time in order to compute zulu time.For example, all of the following time values are equivalent: 2013 - 10 - 07 08:23:19.120 2013 - 10 - 07T08: 23:19.120Z 2013 - 10 - 07 04:23:19.120 - 04:00 2456572.84952685 In formats 4, 7, and 10, the fractional seconds value SS.SSS can have one or more digits following the decimal point.Exactly three digits are shown in the examples because only the first three digits are significant to the result, but the input string can have fewer or more than three digits and the date / time functions will still operate correctly.Similarly, format 12 is shown with 10 significant digits, but the date / time functions will really accept as many or as few digits as are necessary to represent the Julian day number. |
modifier1 (Optional) | [String] One or more modifier. The time value can be followed by zero or more modifiers that alter date and/or time. Each modifier is a transformation that is applied to the time value to its left. Modifiers are applied from left to right; order is important. The available modifiers are as follows. 1. NNN days 2. NNN hours 3. NNN minutes 4. NNN.NNNN seconds 5. NNN months 6. NNN years 7. start of month 8. start of year 9. start of day 10. weekday N 11. unixepoch 12. localtime 13. utc The first six modifiers (1 through 6) simply add the specified amount of time to the date and time specified by the arguments to the left. The 's' character at the end of the modifier names is optional. Note that '±NNN months' works by rendering the original date into the YYYY-MM-DD format, adding the ±NNN to the MM month value, then normalizing the result. Thus, for example, the data 2001-03-31 modified by '+1 month' initially yields 2001-04-31, but April only has 30 days so the date is normalized to 2001-05-01. A similar effect occurs when the original date is February 29 of a leapyear and the modifier is ±N years where N is not a multiple of four. The 'start of' modifiers (7 through 9) shift the date backwards to the beginning of the current month, year or day. The 'weekday' modifier advances the date forward, if necessary, to the next date where the weekday number is N. Sunday is 0, Monday is 1, and so forth. If the date is already on the desired weekday, the 'weekday' modifier leaves the date unchanged. The 'unixepoch' modifier (11) only works if it immediately follows a time value in the DDDDDDDDDD format. This modifier causes the DDDDDDDDDD to be interpreted not as a Julian day number as it normally would be, but as Unix Time - the number of seconds since 1970. If the 'unixepoch' modifier does not follow a time value of the form DDDDDDDDDD which expresses the number of seconds since 1970 or if other modifiers separate the 'unixepoch' modifier from prior DDDDDDDDDD then the behavior is undefined. For SQLite versions before 3.16.0 (2017-01-02), the 'unixepoch' modifier only works for dates between 0000-01-01 00:00:00 and 5352-11-01 10:52:47 (unix times of -62167219200 through 106751991167). The 'localtime' modifier (12) assumes the time value to its left is in Universal Coordinated Time (UTC) and adjusts that time value so that it is in localtime. If 'localtime' follows a time that is not UTC, then the behavior is undefined. The 'utc' modifier is the opposite of 'localtime'. 'utc' assumes that the time value to its left is in the local timezone and adjusts that time value to be in UTC. If the time to the left is not in localtime, then the result of 'utc' is undefined. |
modifier2 (Optional) | [String] same as above |
modifier3 (Optional) | [String] same as above |
modifier4 (Optional) | [String] same as above |
modifier5 (Optional) | [String] same as above |
modifier6 (Optional) | [String] same as above |
modifier7 (Optional) | [String] same as above |
modifier8 (Optional) | [String] same as above |
Examples:
Example | Sample Output | Description |
---|---|---|
| --see SQL comment for output-- |
TIME
Syntax:TIME(timestring[,modifier1][,modifier2][,modifier3][,modifier4][,modifier5][,modifier6][,modifier7][,modifier8])
Parameters:
Name | Description |
---|---|
timestring | [String] Valid time string format. A time value can be in any of the following formats shown below. The value is usually a string, though it can be an integer or floating point number in the case of format 12. 1. YYYY-MM-DD 2. YYYY-MM-DD HH:MM 3. YYYY-MM-DD HH:MM:SS 4. YYYY-MM-DD HH:MM:SS.SSS 5. YYYY-MM-DDTHH:MM 6. YYYY-MM-DDTHH:MM:SS 7. YYYY-MM-DDTHH:MM:SS.SSS 8. HH:MM 9. HH:MM:SS 10. HH:MM:SS.SSS 11. now 12. DDDDDDDDDD In formats 5 through 7, the 'T' is a literal character separating the date and the time, as required by ISO-8601. Formats 8 through 10 that specify only a time assume a date of 2000-01-01. Format 11, the string 'now', is converted into the current date and time as obtained from the xCurrentTime method of the sqlite3_vfs object in use. The 'now' argument to date and time functions always returns exactly the same value for multiple invocations within the same sqlite3_step() call. Universal Coordinated Time (UTC) is used. Format 12 is the Julian day number expressed as an integer or floating point value. Formats 2 through 10 may be optionally followed by a timezone indicator of the form '[+-]HH:MM' or just 'Z'.The date and time functions use UTC or 'zulu' time internally, and so the 'Z' suffix is a no - op.Any non - zero 'HH:MM' suffix is subtracted from the indicated date and time in order to compute zulu time.For example, all of the following time values are equivalent: 2013 - 10 - 07 08:23:19.120 2013 - 10 - 07T08: 23:19.120Z 2013 - 10 - 07 04:23:19.120 - 04:00 2456572.84952685 In formats 4, 7, and 10, the fractional seconds value SS.SSS can have one or more digits following the decimal point.Exactly three digits are shown in the examples because only the first three digits are significant to the result, but the input string can have fewer or more than three digits and the date / time functions will still operate correctly.Similarly, format 12 is shown with 10 significant digits, but the date / time functions will really accept as many or as few digits as are necessary to represent the Julian day number. |
modifier1 (Optional) | [String] One or more modifier. The time value can be followed by zero or more modifiers that alter date and/or time. Each modifier is a transformation that is applied to the time value to its left. Modifiers are applied from left to right; order is important. The available modifiers are as follows. 1. NNN days 2. NNN hours 3. NNN minutes 4. NNN.NNNN seconds 5. NNN months 6. NNN years 7. start of month 8. start of year 9. start of day 10. weekday N 11. unixepoch 12. localtime 13. utc The first six modifiers (1 through 6) simply add the specified amount of time to the date and time specified by the arguments to the left. The 's' character at the end of the modifier names is optional. Note that '±NNN months' works by rendering the original date into the YYYY-MM-DD format, adding the ±NNN to the MM month value, then normalizing the result. Thus, for example, the data 2001-03-31 modified by '+1 month' initially yields 2001-04-31, but April only has 30 days so the date is normalized to 2001-05-01. A similar effect occurs when the original date is February 29 of a leapyear and the modifier is ±N years where N is not a multiple of four. The 'start of' modifiers (7 through 9) shift the date backwards to the beginning of the current month, year or day. The 'weekday' modifier advances the date forward, if necessary, to the next date where the weekday number is N. Sunday is 0, Monday is 1, and so forth. If the date is already on the desired weekday, the 'weekday' modifier leaves the date unchanged. The 'unixepoch' modifier (11) only works if it immediately follows a time value in the DDDDDDDDDD format. This modifier causes the DDDDDDDDDD to be interpreted not as a Julian day number as it normally would be, but as Unix Time - the number of seconds since 1970. If the 'unixepoch' modifier does not follow a time value of the form DDDDDDDDDD which expresses the number of seconds since 1970 or if other modifiers separate the 'unixepoch' modifier from prior DDDDDDDDDD then the behavior is undefined. For SQLite versions before 3.16.0 (2017-01-02), the 'unixepoch' modifier only works for dates between 0000-01-01 00:00:00 and 5352-11-01 10:52:47 (unix times of -62167219200 through 106751991167). The 'localtime' modifier (12) assumes the time value to its left is in Universal Coordinated Time (UTC) and adjusts that time value so that it is in localtime. If 'localtime' follows a time that is not UTC, then the behavior is undefined. The 'utc' modifier is the opposite of 'localtime'. 'utc' assumes that the time value to its left is in the local timezone and adjusts that time value to be in UTC. If the time to the left is not in localtime, then the result of 'utc' is undefined. |
modifier2 (Optional) | [String] same as above |
modifier3 (Optional) | [String] same as above |
modifier4 (Optional) | [String] same as above |
modifier5 (Optional) | [String] same as above |
modifier6 (Optional) | [String] same as above |
modifier7 (Optional) | [String] same as above |
modifier8 (Optional) | [String] same as above |
Examples:
Example | Sample Output | Description |
---|---|---|
| --see SQL comment for output-- |
JULIANDAY
Syntax:JULIANDAY(timestring[,modifier1][,modifier2][,modifier3][,modifier4][,modifier5][,modifier6][,modifier7][,modifier8])
Parameters:
Name | Description |
---|---|
timestring | [String] Valid time string format. A time value can be in any of the following formats shown below. The value is usually a string, though it can be an integer or floating point number in the case of format 12. 1. YYYY-MM-DD 2. YYYY-MM-DD HH:MM 3. YYYY-MM-DD HH:MM:SS 4. YYYY-MM-DD HH:MM:SS.SSS 5. YYYY-MM-DDTHH:MM 6. YYYY-MM-DDTHH:MM:SS 7. YYYY-MM-DDTHH:MM:SS.SSS 8. HH:MM 9. HH:MM:SS 10. HH:MM:SS.SSS 11. now 12. DDDDDDDDDD In formats 5 through 7, the 'T' is a literal character separating the date and the time, as required by ISO-8601. Formats 8 through 10 that specify only a time assume a date of 2000-01-01. Format 11, the string 'now', is converted into the current date and time as obtained from the xCurrentTime method of the sqlite3_vfs object in use. The 'now' argument to date and time functions always returns exactly the same value for multiple invocations within the same sqlite3_step() call. Universal Coordinated Time (UTC) is used. Format 12 is the Julian day number expressed as an integer or floating point value. Formats 2 through 10 may be optionally followed by a timezone indicator of the form '[+-]HH:MM' or just 'Z'.The date and time functions use UTC or 'zulu' time internally, and so the 'Z' suffix is a no - op.Any non - zero 'HH:MM' suffix is subtracted from the indicated date and time in order to compute zulu time.For example, all of the following time values are equivalent: 2013 - 10 - 07 08:23:19.120 2013 - 10 - 07T08: 23:19.120Z 2013 - 10 - 07 04:23:19.120 - 04:00 2456572.84952685 In formats 4, 7, and 10, the fractional seconds value SS.SSS can have one or more digits following the decimal point.Exactly three digits are shown in the examples because only the first three digits are significant to the result, but the input string can have fewer or more than three digits and the date / time functions will still operate correctly.Similarly, format 12 is shown with 10 significant digits, but the date / time functions will really accept as many or as few digits as are necessary to represent the Julian day number. |
modifier1 (Optional) | [String] One or more modifier. The time value can be followed by zero or more modifiers that alter date and/or time. Each modifier is a transformation that is applied to the time value to its left. Modifiers are applied from left to right; order is important. The available modifiers are as follows. 1. NNN days 2. NNN hours 3. NNN minutes 4. NNN.NNNN seconds 5. NNN months 6. NNN years 7. start of month 8. start of year 9. start of day 10. weekday N 11. unixepoch 12. localtime 13. utc The first six modifiers (1 through 6) simply add the specified amount of time to the date and time specified by the arguments to the left. The 's' character at the end of the modifier names is optional. Note that '±NNN months' works by rendering the original date into the YYYY-MM-DD format, adding the ±NNN to the MM month value, then normalizing the result. Thus, for example, the data 2001-03-31 modified by '+1 month' initially yields 2001-04-31, but April only has 30 days so the date is normalized to 2001-05-01. A similar effect occurs when the original date is February 29 of a leapyear and the modifier is ±N years where N is not a multiple of four. The 'start of' modifiers (7 through 9) shift the date backwards to the beginning of the current month, year or day. The 'weekday' modifier advances the date forward, if necessary, to the next date where the weekday number is N. Sunday is 0, Monday is 1, and so forth. If the date is already on the desired weekday, the 'weekday' modifier leaves the date unchanged. The 'unixepoch' modifier (11) only works if it immediately follows a time value in the DDDDDDDDDD format. This modifier causes the DDDDDDDDDD to be interpreted not as a Julian day number as it normally would be, but as Unix Time - the number of seconds since 1970. If the 'unixepoch' modifier does not follow a time value of the form DDDDDDDDDD which expresses the number of seconds since 1970 or if other modifiers separate the 'unixepoch' modifier from prior DDDDDDDDDD then the behavior is undefined. For SQLite versions before 3.16.0 (2017-01-02), the 'unixepoch' modifier only works for dates between 0000-01-01 00:00:00 and 5352-11-01 10:52:47 (unix times of -62167219200 through 106751991167). The 'localtime' modifier (12) assumes the time value to its left is in Universal Coordinated Time (UTC) and adjusts that time value so that it is in localtime. If 'localtime' follows a time that is not UTC, then the behavior is undefined. The 'utc' modifier is the opposite of 'localtime'. 'utc' assumes that the time value to its left is in the local timezone and adjusts that time value to be in UTC. If the time to the left is not in localtime, then the result of 'utc' is undefined. |
modifier2 (Optional) | [String] same as above |
modifier3 (Optional) | [String] same as above |
modifier4 (Optional) | [String] same as above |
modifier5 (Optional) | [String] same as above |
modifier6 (Optional) | [String] same as above |
modifier7 (Optional) | [String] same as above |
modifier8 (Optional) | [String] same as above |
Examples:
Example | Sample Output | Description |
---|---|---|
| --see SQL comment for output-- |
STRFTIME
Syntax:STRFTIME(format,timestring[,modifier1][,modifier2][,modifier3][,modifier4][,modifier5][,modifier6][,modifier7][,modifier8])
Parameters:
Name | Description |
---|---|
format | [String] The format string supports following substitutions: %d day of month: 00 %f fractional seconds: SS.SSS %H hour: 00-24 %j day of year: 001-366 %J Julian day number %m month: 01-12 %M minute: 00-59 %s seconds since 1970-01-01 %S seconds: 00-59 %w day of week 0-6 with Sunday==0 %W week of year: 00-53 %Y year: 0000-9999 %% % |
timestring | [String] Valid time string format. A time value can be in any of the following formats shown below. The value is usually a string, though it can be an integer or floating point number in the case of format 12. 1. YYYY-MM-DD 2. YYYY-MM-DD HH:MM 3. YYYY-MM-DD HH:MM:SS 4. YYYY-MM-DD HH:MM:SS.SSS 5. YYYY-MM-DDTHH:MM 6. YYYY-MM-DDTHH:MM:SS 7. YYYY-MM-DDTHH:MM:SS.SSS 8. HH:MM 9. HH:MM:SS 10. HH:MM:SS.SSS 11. now 12. DDDDDDDDDD In formats 5 through 7, the 'T' is a literal character separating the date and the time, as required by ISO-8601. Formats 8 through 10 that specify only a time assume a date of 2000-01-01. Format 11, the string 'now', is converted into the current date and time as obtained from the xCurrentTime method of the sqlite3_vfs object in use. The 'now' argument to date and time functions always returns exactly the same value for multiple invocations within the same sqlite3_step() call. Universal Coordinated Time (UTC) is used. Format 12 is the Julian day number expressed as an integer or floating point value. Formats 2 through 10 may be optionally followed by a timezone indicator of the form '[+-]HH:MM' or just 'Z'.The date and time functions use UTC or 'zulu' time internally, and so the 'Z' suffix is a no - op.Any non - zero 'HH:MM' suffix is subtracted from the indicated date and time in order to compute zulu time.For example, all of the following time values are equivalent: 2013 - 10 - 07 08:23:19.120 2013 - 10 - 07T08: 23:19.120Z 2013 - 10 - 07 04:23:19.120 - 04:00 2456572.84952685 In formats 4, 7, and 10, the fractional seconds value SS.SSS can have one or more digits following the decimal point.Exactly three digits are shown in the examples because only the first three digits are significant to the result, but the input string can have fewer or more than three digits and the date / time functions will still operate correctly.Similarly, format 12 is shown with 10 significant digits, but the date / time functions will really accept as many or as few digits as are necessary to represent the Julian day number. |
modifier1 (Optional) | [String] One or more modifier. The time value can be followed by zero or more modifiers that alter date and/or time. Each modifier is a transformation that is applied to the time value to its left. Modifiers are applied from left to right; order is important. The available modifiers are as follows. 1. NNN days 2. NNN hours 3. NNN minutes 4. NNN.NNNN seconds 5. NNN months 6. NNN years 7. start of month 8. start of year 9. start of day 10. weekday N 11. unixepoch 12. localtime 13. utc The first six modifiers (1 through 6) simply add the specified amount of time to the date and time specified by the arguments to the left. The 's' character at the end of the modifier names is optional. Note that '±NNN months' works by rendering the original date into the YYYY-MM-DD format, adding the ±NNN to the MM month value, then normalizing the result. Thus, for example, the data 2001-03-31 modified by '+1 month' initially yields 2001-04-31, but April only has 30 days so the date is normalized to 2001-05-01. A similar effect occurs when the original date is February 29 of a leapyear and the modifier is ±N years where N is not a multiple of four. The 'start of' modifiers (7 through 9) shift the date backwards to the beginning of the current month, year or day. The 'weekday' modifier advances the date forward, if necessary, to the next date where the weekday number is N. Sunday is 0, Monday is 1, and so forth. If the date is already on the desired weekday, the 'weekday' modifier leaves the date unchanged. The 'unixepoch' modifier (11) only works if it immediately follows a time value in the DDDDDDDDDD format. This modifier causes the DDDDDDDDDD to be interpreted not as a Julian day number as it normally would be, but as Unix Time - the number of seconds since 1970. If the 'unixepoch' modifier does not follow a time value of the form DDDDDDDDDD which expresses the number of seconds since 1970 or if other modifiers separate the 'unixepoch' modifier from prior DDDDDDDDDD then the behavior is undefined. For SQLite versions before 3.16.0 (2017-01-02), the 'unixepoch' modifier only works for dates between 0000-01-01 00:00:00 and 5352-11-01 10:52:47 (unix times of -62167219200 through 106751991167). The 'localtime' modifier (12) assumes the time value to its left is in Universal Coordinated Time (UTC) and adjusts that time value so that it is in localtime. If 'localtime' follows a time that is not UTC, then the behavior is undefined. The 'utc' modifier is the opposite of 'localtime'. 'utc' assumes that the time value to its left is in the local timezone and adjusts that time value to be in UTC. If the time to the left is not in localtime, then the result of 'utc' is undefined. |
modifier2 (Optional) | [String] same as above |
modifier3 (Optional) | [String] same as above |
modifier4 (Optional) | [String] same as above |
modifier5 (Optional) | [String] same as above |
modifier6 (Optional) | [String] same as above |
modifier7 (Optional) | [String] same as above |
modifier8 (Optional) | [String] same as above |
Examples:
Example | Sample Output | Description |
---|---|---|
| --see SQL comment for output-- |
Advanced Functions
NULLIF
Syntax:NULLIF(expr1,expr2)
Parameters:
Name | Description |
---|---|
expr1 | [String] first value or expression to check. |
expr2 | [String] second value or expression to check. |
Examples:
Example | Sample Output | Description |
---|---|---|
| A | When both values are not same then first value is returned |
| (NULL) | When both values same then null is returned |
| A | When both values same then null is returned |
IFNULL
Syntax:IFNULL(expr1,expr2)
Parameters:
Name | Description |
---|---|
expr1 | [String] first value or expression to check. |
expr2 | [String] second value or expression to check. |
Examples:
Example | Sample Output | Description |
---|---|---|
| ABCD | |
| ABCD | |
| N/A | |
| (NULL) | |
| N/A | Returns N/A because first expression produces null |
COALESCE
Syntax:COALESCE(expr1,expr2[,expr3][,expr4][,expr5][,expr6][,expr7][,expr8])
Parameters:
Name | Description |
---|---|
expr1 | [String] first value or expression to check. |
expr2 | [String] second value or expression to check. |
expr3 (Optional) | [String] third value or expression to check. |
expr4 (Optional) | [String] forth value or expression to check. |
expr5 (Optional) | [String] forth value or expression to check. |
expr6 (Optional) | [String] forth value or expression to check. |
expr7 (Optional) | [String] forth value or expression to check. |
expr8 (Optional) | [String] forth value or expression to check. |
Examples:
Example | Sample Output | Description |
---|---|---|
| A | Returns A - first non-null value or expression |
| 2 | Returns 2 - first non-null value or expression |
| B | Returns B - first non-null value or expression. Since first arg is null it returns seconds arg |