Hubspot Connector
Documentation
Version: 5
Documentation

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])
Search for string pattern using Regular expression and returns a new string with replaced value

Parameters:

NameDescription
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:

ExampleSample OutputDescription
SELECT REGEX_REPLACE('I like Cap0-red and Cap1-blue','Cap(\d+)-(\w+)','Hat$1-$2');
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
SELECT REGEX_REPLACE('I like CAP0-red and cap1-blue','CaP(\d+)-(\w+)','Hat$1-$2',true);
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])
Checks if specified search pattern found in the input string. Returns 1 if pattern found else 0

Parameters:

NameDescription
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:

ExampleSample OutputDescription
SELECT REGEX_ISMATCH('AA11BB','\d+');
1
Search for number anywhere in string
SELECT REGEX_ISMATCH('11AABB','^\d+');
1
Check if string starting with numeric value
SELECT REGEX_ISMATCH('AABB11','\d+$');
1
Check if string ending with numeric value
SELECT REGEX_ISMATCH('11','^\d+$');
1
Check if string is integer value
SELECT REGEX_ISMATCH('AA','^\d+$');
0
Check if string is integer value
SELECT REGEX_ISMATCH('AA00','aa\d+');
0
Search for string
SELECT REGEX_ISMATCH('AA00','aa\d+',true);
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])
This function search lower case string.

Parameters:

NameDescription
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:

ExampleSample OutputDescription
SELECT REGEX_SEARCH('>> 00AA 01BB 02CC 03DD <<<','(\d+)(\w+)');
00AA
Search for alpha-numeric. Return first match if no match index specified (Default=0 i.e. first))
SELECT REGEX_SEARCH('>> 00AA 01BB 02CC 03DD <<<','(\d+)(\w+)',-1);
03DD
Return last match
SELECT REGEX_SEARCH('>> 00AA 01BB 02CC 03DD <<<','(\d+)(\w+)',0,2);
03
Return first match and extract 1st group of pattern (i.e. \d+)
SELECT REGEX_SEARCH('>> 00AA 01BB 02CC 03DD <<<','(\d+)(\w+)',0,2);
DD
Return first match and extract 2nd group of pattern (i.e. \w+)
SELECT REGEX_SEARCH('>> 00AA 01BB 02CC 03DD <<<','(\d+)(\w+)',*);
00AA11BB22CC33DD
Return all matches and join without any separator
SELECT REGEX_SEARCH('>> 00AA 01BB 02CC 03DD <<<','(\d+)(\w+)',*,0,false,false,'-');
00AA-11BB-22CC-33DD
Return all matches and join them by dash (-)
SELECT REGEX_SEARCH('>> 00AA 01BB 02CC 03DD <<<','(\d+)(\w+)',*,2,false,false,'-');
AA-BB-CC-DD
Return all matches and join them by dash (-)
SELECT REGEX_SEARCH('>> 00AA 01BB 02CC 03DD <<<','(\d+)(\w+)',*,2);
AABBCCDD
Return 2nd group of all matches and join string together

BASE64_TO_BYTES

Syntax:
BASE64_TO_BYTES(base64_string)
This function returns byte array from specified Base64 string.

Parameters:

NameDescription
base64_string[String] Input base64 string which you want to convert to byte array.

Examples:

ExampleSample OutputDescription
SELECT BASE64_TO_BYTES('QWJjZA==');
Bytes[] for Abcd

CONCAT

Syntax:
CONCAT(string1,string2,string3,stringN)
This function concat multiple strings. You can also use || operator rather than this function

Parameters:

NameDescription
string1[String] First string
string2[String] Second string
string3[String] Third string
stringN[String] Third string

Examples:

ExampleSample OutputDescription
SELECT CONCAT('AA','BB','CC');
AABBCC
Concat multiple strings
SELECT CONCAT('AA',null,'BB');
(NULL)
yields null when one or more values are null
SELECT CONCAT('AA','BB','CC','DD','EE','FF');
AABBCCDDEEFF
Concat many strings using function
SELECT CONCAT('AA' || 'BB' || 'CC' || 'DD' || 'EE' || 'FF');
AABBCCDDEEFF
Concat many strings using || operator
SELECT CONCAT('AA' || null || 'CC' );
(NULL)
Concat many strings using || operator yields null when one or more values are null

LOWER

Syntax:
LOWER(input_text)
This function returns lower case string.

Parameters:

NameDescription
input_text[String] Input string which you want to change to lower case.

Examples:

ExampleSample OutputDescription
SELECT LOWER('AbCd');
abcd

UPPER

Syntax:
UPPER(input_text)
This function returns upper case string.

Parameters:

NameDescription
input_text[String] Input string which you want to change to upper case.

Examples:

ExampleSample OutputDescription
SELECT UPPER('AbCd');
ABCD

SUBSTR

Syntax:
SUBSTR(input_text,start_position,length)
Extract and returns a substring with a predefined length starting at a specified position in a source string.

Parameters:

NameDescription
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:

ExampleSample OutputDescription
SELECT SUBSTR('ZappySys PowerPack', 1, 8);
ZappySys
SELECT SUBSTR('ZappySys PowerPack', 8, -8);
ZappySys
SELECT SUBSTR('ZappySys PowerPack', -1, 9);
PowerPack
Reverse start position (-ve number)

TRIM

Syntax:
TRIM(input_text[,characters_to_trim])
Trim spaces or specified characters from the left side and right side of the input string and returns a new string.

Parameters:

NameDescription
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:

ExampleSample OutputDescription
SELECT TRIM('  *** xxABCxx ***  ');
*** xxABCxx ***
Trims space around input string
SELECT TRIM('  *** xxABCxx ***  ','x *');
ABC
Trims space, tab, x or * characters around input string
SELECT TRIM('  *** xxABCxx ***  ','x *' || char(9) || char(13) || char(10));
ABC
Trims x,*,space, tab (i.e. char(9)) or new line (i.e. CR LF=char(13), char(10)) characters around input string
SELECT TRIM(null);
(NULL)

LTRIM

Syntax:
LTRIM(input_text[,characters_to_trim])
Trim spaces or specified characters from the left side of the input string and returns a new string.

Parameters:

NameDescription
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:

ExampleSample OutputDescription
SELECT LTRIM('  *** xxABCxx ***  ');
*** xxABCxx ***  
Trims space from the left side of the input string
SELECT LTRIM('  *** xxABCxx ***  ','x *');
ABCxx ***  
Trims space, tab, x or * characters from the left side of the input string
SELECT LTRIM('  *** xxABCxx ***  ','x *' || char(9) || char(13) || char(10));
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
SELECT LTRIM(null);
(NULL)

RTRIM

Syntax:
RTRIM(input_text[,characters_to_trim])
Trim spaces or specified characters from the right side of the input string and returns a new string.

Parameters:

NameDescription
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:

ExampleSample OutputDescription
SELECT RTRIM('  *** xxABCxx ***  ');
  *** xxABCxx ***
Trims space from the right side of the input string
SELECT RTRIM('  *** xxABCxx ***  ','x *');
 ***  xxABC
Trims space, tab, x or * characters from the right side of the input string
SELECT RTRIM('  *** xxABCxx ***  ','x *' || char(9) || char(13) || char(10));
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
SELECT RTRIM(null);
(NULL)

LENGTH

Syntax:
LENGTH(input_text)
Returns total number of characters in the input string.

Parameters:

NameDescription
input_text[String] Input text for which you like calculate length.

Examples:

ExampleSample OutputDescription
SELECT LENGTH('ABC');
3
SELECT LENGTH('A B C');
5
SELECT LENGTH(null);
(NULL)

REPLACE

Syntax:
REPLACE(input_text,search_for,replace_with)
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.

Parameters:

NameDescription
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:

ExampleSample OutputDescription
SELECT REPLACE('The sky is green','green','blue');
The sky is blue
Search for word 'green' and replace with word 'blue'
SELECT REPLACE('The sky is green',null,'blue');
(NULL)
Any null input results in null

INSTR

Syntax:
INSTR(input_text,search_for)
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.

Parameters:

NameDescription
input_text[String] Input text for which you like search and replace.
search_for[String] Text you like to search in input text.

Examples:

ExampleSample OutputDescription
SELECT INSTR('The sky is green','The');
1
Returns position of first occurrence of word 'The'
SELECT INSTR('The sky is green','is');
9
Returns position of first occurrence of word 'is'
SELECT INSTR('The sky is green','Fake');
0
Returns 0 if word not found

CHAR

Syntax:
CHAR(charcode1[,charcode2][,charcode3][,charcode4][,charcode5][,charcode6][,charcode7][,charcode8])
Returns character(s) for specified numeric code(s).

Parameters:

NameDescription
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:

ExampleSample OutputDescription
SELECT CHAR(65);
A
Returns Ascii char A
SELECT CHAR(66);
B
Returns Ascii char B
SELECT CHAR(2344);
Returns Unicode character न
SELECT CHAR(65,66,67);
ABC
Returns string from multiple ascii character codes
SELECT CHAR(2344,2351,2344);
नयन
Returns string from multiple unicode character codes
SELECT CHAR(13,10);
{newline}
Returns New line char which is made from 2 chars CR+LF (i.e. char(13) + char(10))
SELECT CHAR(9);
{tab}
Returns Tab character
SELECT CHAR(null);
(NULL)

HEX

Syntax:
HEX(input_text)
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.

Parameters:

NameDescription
input_text[String] Input text for which you like generate HEX string.

Examples:

ExampleSample OutputDescription
SELECT HEX('A');
41
Hex value of char A
SELECT HEX('*');
2A
Hex value of char *
SELECT HEX('ABC*');
4142432A
Hex value of 4 chars A,B,C and *
SELECT HEX(null);
(NULL)

RANDOM_STRING

Syntax:
RANDOM_STRING([length])
Returns random string with specified length.

Parameters:

NameDescription
length (Optional)[String] range start number for random number generation.

Examples:

ExampleSample OutputDescription
SELECT RANDOM_STRING();
AbCd@!4124Xyz@%)*123
Returns random string of 20 characters by default
SELECT RANDOM_STRING(3);
Abc
Returns random string of 3 characters

RANDOM_IMAGE

Syntax:
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.

Examples:

ExampleSample OutputDescription
SELECT FILE_WRITE_BINARY(RANDOM_IMAGE(),'c:\temp\dump.png')
byte[...]
Byte array of some random image which can be saved to disk by calling
SELECT BYTES_TO_BASE64(RANDOM_IMAGE())
iVBORw0KG....{many more}....rkJggg==
Base64 of byte[] returned from RANDOM_IMAGE function

SOUNDEX

Syntax:
SOUNDEX(input_text)
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.

Parameters:

NameDescription
input_text[String] Input text for which you like return soundex encoded string.

Examples:

ExampleSample OutputDescription
SELECT SOUNDEX('New york');
N620
SELECT SOUNDEX('newyork');
N620
SELECT SOUNDEX('Chicago');
C220
SELECT SOUNDEX('Chicagoo');
C220
SELECT SOUNDEX('Cheecago');
C220
SELECT SOUNDEX('Ceecago');
C220
SELECT SOUNDEX(null);
?000
For null input returns ?000

Other Functions


SLEEP

Syntax:
SLEEP(delay_milliseconds)
Adds delay for specified amount of milliseconds.

Parameters:

NameDescription
delay_milliseconds[Int32] Specifies how many milliseconds delay.

Examples:

ExampleSample OutputDescription
SELECT SLEEP(2000);
1
Sleeps for 2 seconds. Returns 1 if successful

JSON Functions


JSON_VALUE

Syntax:
JSON_VALUE(json_document,jsonpath_expression[,throw_error_if_nomatch])
This function returns single value out of JSON document using JSON Path expression.

Parameters:

NameDescription
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:

ExampleSample OutputDescription
SELECT JSON_VALUE('{location:{city:"Atlanta", country:"USA"}}','$.location.country');
USA
Returns country from location.
SELECT JSON_VALUE('{locationList : [{city:"Atlanta", country:"USA"},{city:"Mumbai", country:"INDIA"}]}','$.locationList[1].country');
INDIA
Returns country of second item from locationList array.
SELECT JSON_VALUE('{locationList : [{city:"Atlanta", country:"USA"},{city:"Mumbai", country:"INDIA"}]}','$.locationList[?(@city=='Mumbai')].country');
INDIA
Returns country of item where city=='Mumbai'.

JSON_ARRAY_FIRST

Syntax:
JSON_ARRAY_FIRST(json_array)
This function returns first element from the JSON array. This is same as calling JSON_ARRAY_NTH for 0 index.

Parameters:

NameDescription
json_array[String] JSON array string from which you like to extract a single value.

Examples:

ExampleSample OutputDescription
SELECT JSON_ARRAY_FIRST('["Jan","Feb","Mar"]');
Jan
Returns 1st element from JSON array

JSON_ARRAY_LAST

Syntax:
JSON_ARRAY_LAST(json_array)
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.

Parameters:

NameDescription
json_array[String] JSON array string from which you like to extract a single value.

Examples:

ExampleSample OutputDescription
SELECT JSON_ARRAY_LAST('["Jan","Feb","Mar"]');
Mar
Returns 3rd element from JSON array

JSON_ARRAY_NTH

Syntax:
JSON_ARRAY_NTH(json_array,index)
This function returns JSON array element for specified position (0 based index).

Parameters:

NameDescription
json_array[String] JSON array string from which you like to extract a single value.
index[String] Array element index (0 based)

Examples:

ExampleSample OutputDescription
SELECT JSON_ARRAY_NTH('["Jan","Feb","Mar"]',0);
Jan
Returns 1st element from JSON array
SELECT JSON_ARRAY_NTH('["Jan","Feb","Mar"]',2);
Mar
Returns 3rd element from JSON array

Encoding Functions


BYTES_TO_BASE64

Syntax:
BYTES_TO_BASE64(byte_array)
This function encodes byte array into base64 string. You can use function like BASE64_TO_BYTES to generate Byte Array.

Parameters:

NameDescription
byte_array[Byte] Input string which you like to encode in base64.

Examples:

ExampleSample OutputDescription
SELECT BYTES_TO_BASE64(BASE64_TO_BYTES('QWJjZA=='));
QWJjZA==
Returns Base64 string from byte array.

TEXT_TO_BASE64

Syntax:
TEXT_TO_BASE64(plain_text)
This function encodes plain text into base64 string.

Parameters:

NameDescription
plain_text[String] Input string which you like to encode in base64.

Examples:

ExampleSample OutputDescription
SELECT TEXT_TO_BASE64('Abcd');
QWJjZA==
Encodes plain text into Base64 string

BASE64_TO_TEXT

Syntax:
BASE64_TO_TEXT(base64_text)
This function decodes base64 string and returns plain text.

Parameters:

NameDescription
base64_text[String] Input string encoded in base64.

Examples:

ExampleSample OutputDescription
SELECT BASE64_TO_TEXT('QWJjZA==');
Abcd
Decodes Base64 text into plain text

FILE_TO_BASE64

Syntax:
FILE_TO_BASE64(filepath_or_url)
This function returns base64 string of file content (binary or text file). Path can be local file or URL

Parameters:

NameDescription
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:

ExampleSample OutputDescription
SELECT FILE_TO_BASE64('c:\temp\image.png');
iVBORw0KGgoAAAANSUhEUgAAABQAAAAUCAIAAAAC64paAAAACXBIWXMAAA7EAAAOxAGVKw4bAAAAB3RJTUUH5QMTASEFcmYpawAAAAd0RVh0QXV0aG9yAKmuzEgAAAAMdEVYdERlc2NyaXB0aW9uABMJISMAAAAKdEVYdENvcHlyaWdodACsD8w6AAAADnRFWHRDcmVhdGlvbiB0aW1lADX3DwkAAAAJdEVYdFNvZnR3YXJlAF1w/zoAAAALdEVYdERpc2NsYWltZXIAt8C0jwAAAAh0RVh0V2FybmluZwDAG+aHAAAAB3RFWHRTb3VyY2UA9f+D6wAAAAh0RVh0Q29tbWVudAD2zJa/AAAABnRFWHRUaXRsZQCo7tInAAAAZUlEQVQ4jWP84+TEQC5gIlsnpZpZ4CzmvXuJ1PPX2RldMwMDA/uGCQR1/gwogLMHg5+RAbLbIACrj7BrZmBg+LGWE87mCP6OVc3A+ZkGAcaA25+ENROTWhgGjZ8x0wZ+wDg0CwMApCQTh3PgswUAAAAASUVORK5CYII=
Returns Base64 encoded text of file content (can be text file or binary data like png, zip, mp3)
SELECT FILE_TO_BASE64('https://zappysys.com/downloads/files/test/small.png');
iVBORw0KGgoAAAANSUhEUgAAABQAAAAUCAIAAAAC64paAAAACXBIWXMAAA7EAAAOxAGVKw4bAAAAB3RJTUUH5QMTASEFcmYpawAAAAd0RVh0QXV0aG9yAKmuzEgAAAAMdEVYdERlc2NyaXB0aW9uABMJISMAAAAKdEVYdENvcHlyaWdodACsD8w6AAAADnRFWHRDcmVhdGlvbiB0aW1lADX3DwkAAAAJdEVYdFNvZnR3YXJlAF1w/zoAAAALdEVYdERpc2NsYWltZXIAt8C0jwAAAAh0RVh0V2FybmluZwDAG+aHAAAAB3RFWHRTb3VyY2UA9f+D6wAAAAh0RVh0Q29tbWVudAD2zJa/AAAABnRFWHRUaXRsZQCo7tInAAAAZUlEQVQ4jWP84+TEQC5gIlsnpZpZ4CzmvXuJ1PPX2RldMwMDA/uGCQR1/gwogLMHg5+RAbLbIACrj7BrZmBg+LGWE87mCP6OVc3A+ZkGAcaA25+ENROTWhgGjZ8x0wZ+wDg0CwMApCQTh3PgswUAAAAASUVORK5CYII=
Returns Base64 encoded text of file from URL

BASE64_TO_FILE

Syntax:
BASE64_TO_FILE(filepath,base64_content)
This function saves bytes of base64 string to file. You can create Text/Binary file out of Base64 string using this function.

Parameters:

NameDescription
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:

ExampleDescription
SELECT BASE64_TO_FILE('c:\temp\image.png','iVBORw0KGgoAAAANSUhEUgAAABQAAAAUCAIAAAAC64paAAAACXBIWXMAAA7EAAAOxAGVKw4bAAAAB3RJTUUH5QMTASEFcmYpawAAAAd0RVh0QXV0aG9yAKmuzEgAAAAMdEVYdERlc2NyaXB0aW9uABMJISMAAAAKdEVYdENvcHlyaWdodACsD8w6AAAADnRFWHRDcmVhdGlvbiB0aW1lADX3DwkAAAAJdEVYdFNvZnR3YXJlAF1w/zoAAAALdEVYdERpc2NsYWltZXIAt8C0jwAAAAh0RVh0V2FybmluZwDAG+aHAAAAB3RFWHRTb3VyY2UA9f+D6wAAAAh0RVh0Q29tbWVudAD2zJa/AAAABnRFWHRUaXRsZQCo7tInAAAAZUlEQVQ4jWP84+TEQC5gIlsnpZpZ4CzmvXuJ1PPX2RldMwMDA/uGCQR1/gwogLMHg5+RAbLbIACrj7BrZmBg+LGWE87mCP6OVc3A+ZkGAcaA25+ENROTWhgGjZ8x0wZ+wDg0CwMApCQTh3PgswUAAAAASUVORK5CYII=');
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])
This function reads text from local file. To read data from binary file use FILE_TO_BASE64 function or FILE_READ_BINARY function

Parameters:

NameDescription
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:

ExampleSample OutputDescription
SELECT FILE_READ_TEXT('c:\temp\test.txt');
abcd
Returns text from a specified file (assuming file content is abcd)
SELECT FILE_READ_TEXT('c:\temp\some-bad-path.txt');
--ERROR--
Pass bad path
SELECT FILE_READ_TEXT('c:\temp\some-bad-path.txt',1);
(NULL)
(Set continueOnError=true | 1) Returns null rather than error if file not found

FILE_READ_BINARY

Syntax:
FILE_READ_BINARY(filepath[,continueOnError])
This function reads data from local file as byte array (i.e. byte[]). To read data as text use FILE_READ_TEXT function.

Parameters:

NameDescription
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:

ExampleSample OutputDescription
SELECT FILE_READ_BINARY('c:\temp\test.txt');
abcd
Returns text from a specified file (assuming file content is abcd)
SELECT FILE_READ_BINARY('c:\temp\some-bad-path.txt');
--ERROR--
Pass bad path
SELECT FILE_READ_BINARY('c:\temp\some-bad-path.txt',1);
(NULL)
(Set continueOnError=true | 1) Returns null rather than error if file not found

FILE_WRITE_TEXT

Syntax:
FILE_WRITE_TEXT(filepath,content[,failIfExists])
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.

Parameters:

NameDescription
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:

ExampleSample OutputDescription
SELECT FILE_WRITE_TEXT('AbCd','c:\temp\test.txt');
4
Returns length of text written to the file

FILE_WRITE_BINARY

Syntax:
FILE_WRITE_BINARY(filepath,byte_array[,failIfExists])
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.

Parameters:

NameDescription
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:

ExampleSample OutputDescription
SELECT FILE_WRITE_BINARY(BASE64_TO_BYTES('QWJjZA=='));
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])
This function returns hash of specified input text. You can get hash in HEX or BASE64 format by supplying additional arguments

Parameters:

NameDescription
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:

ExampleSample OutputDescription
SELECT HASH_TEXT('md5','abc123');
e99a18c428cb38d5f260853678922e03
Calculate MD5 Hash - Output in Hex format - lower case (default parameters omitted - Output_format=Hex, uppercase_hex=0)
SELECT HASH_TEXT('md5','abc123','hex',0);
e99a18c428cb38d5f260853678922e03
Calculate MD5 Hash - Output in Hex format - lower case
SELECT HASH_TEXT('md5','abc123','hex',1);
E99A18C428CB38D5F260853678922E03
Calculate MD5 Hash - Output in Hex format - upper case
SELECT HASH_TEXT('md5','abc123','base64');
6ZoYxCjLONXyYIU2eJIuAw==
Calculate MD5 Hash - Output in Base64 format
SELECT HASH_TEXT('sha256','abc123');
6ca13d52ca70c883e0f0bb101e425a89e8624de51db2d2392593af6a84118090
Calculate SHA256 Hash - Output in Hex format - lower case
SELECT HASH_TEXT('sha256','abc123','base64');
bKE9UspwyIPg8LsQHkJaiehiTeUdstI5JZOvaoQRgJA=
Calculate SHA256 Hash - Output in Base64 format

HASH_FILE

Syntax:
HASH_FILE(algorithm,input_text[,output_format][,uppercase_hex])
This function returns hash of specified input file(text or binary). You can get hash in HEX or BASE64 format by supplying additional arguments

Parameters:

NameDescription
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:

ExampleSample OutputDescription
SELECT HASH_FILE('md5','c:\myfile.txt');
e99a18c428cb38d5f260853678922e03
(input file text: abc123) Calculate MD5 Hash - Output in Hex format - lower case (default parameters omitted - Output_format=Hex, uppercase_hex=0)
SELECT HASH_FILE('md5','c:\myfile.txt','hex',0);
e99a18c428cb38d5f260853678922e03
(input file text: abc123) Calculate MD5 Hash - Output in Hex format - lower case
SELECT HASH_FILE('md5','c:\myfile.txt','hex',1);
E99A18C428CB38D5F260853678922E03
(input file text: abc123) Calculate MD5 Hash - Output in Hex format - upper case
SELECT HASH_FILE('md5','c:\myfile.txt','base64');
6ZoYxCjLONXyYIU2eJIuAw==
(input file text: abc123) Calculate MD5 Hash - Output in Base64 format
SELECT HASH_FILE('sha256','c:\myfile.txt');
6ca13d52ca70c883e0f0bb101e425a89e8624de51db2d2392593af6a84118090
(input file text: abc123) Calculate SHA256 Hash - Output in Hex format - lower case
SELECT HASH_FILE('sha256','c:\myfile.txt','base64');
bKE9UspwyIPg8LsQHkJaiehiTeUdstI5JZOvaoQRgJA=
(input file text: abc123) Calculate SHA256 Hash - Output in Base64 format

Number Functions


ABS

Syntax:
ABS(input_number)
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.

Parameters:

NameDescription
input_number[String] input number for which you like to get absolute value.

Examples:

ExampleSample OutputDescription
SELECT ABS(-4);
4
SELECT ABS(4);
4
SELECT ABS(null);
(NULL)
Null input returns null

SIGN

Syntax:
SIGN(input_number)
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.

Parameters:

NameDescription
input_number[String] input number for which you like to get sign value.

Examples:

ExampleSample OutputDescription
SELECT SIGN(-4);
-1
SELECT SIGN(4);
1
SELECT SIGN(+4);
1
SELECT SIGN(0);
0
SELECT SIGN(null);
(NULL)
Null input returns null

ROUND

Syntax:
ROUND(input_number[,digits])
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.

Parameters:

NameDescription
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:

ExampleSample OutputDescription
SELECT ROUND();
-123234
Can be any random number between -9223372036854775808 and +9223372036854775807
SELECT ROUND(0);
123234
Can be any random number between 0 and +9223372036854775807
SELECT ROUND(0,10);
5
Can be any random number between 0 and +10
SELECT ROUND(-10,10);
5
Can be any random number between -10 and +10

RANDOM_INT

Syntax:
RANDOM_INT([start][,end])
Returns random number between specified numbers. If boundary not specified then returns number between -2147483648 and +2147483647.

Parameters:

NameDescription
start (Optional)[String] range start number for random number generation.
end (Optional)[String] range end number for random number generation.

Examples:

ExampleSample OutputDescription
SELECT RANDOM_INT();
-123234
Can be any random number between -2147483648 and +2147483647
SELECT RANDOM_INT(0);
123234
Can be any random number between 0 and +2147483647
SELECT RANDOM_INT(0,10);
5
Can be any random number between 0 and +10
SELECT RANDOM_INT(-10,10);
5
Can be any random number between -10 and +10

RANDOM_LONG

Syntax:
RANDOM_LONG([start][,end])
Returns random number between specified numbers. If boundary not specified then returns number between -9223372036854775808 and +9223372036854775807.

Parameters:

NameDescription
start (Optional)[String] range start number for random number generation.
end (Optional)[String] range end number for random number generation.

Examples:

ExampleSample OutputDescription
SELECT RANDOM_LONG();
-123234
Can be any random number between -9223372036854775808 and +9223372036854775807
SELECT RANDOM_LONG(0);
123234
Can be any random number between 0 and +9223372036854775807
SELECT RANDOM_LONG(0,10);
5
Can be any random number between 0 and +10
SELECT RANDOM_LONG(-10,10);
5
Can be any random number between -10 and +10

RANDOM_INT

Syntax:
RANDOM_INT([start][,end])
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.

Parameters:

NameDescription
start (Optional)[String] range start number for random number generation.
end (Optional)[String] range end number for random number generation.

Examples:

ExampleSample OutputDescription
SELECT RANDOM_INT();
-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
SELECT RANDOM_INT(0);
123234
Can be any random number between 0 and +79,228,162,514,264,337,593,543,950,335
SELECT RANDOM_INT(0,10);
5
Can be any random number between 0 and +10
SELECT RANDOM_INT(-10,10);
5
Can be any random number between -10 and +10

Aggregate Functions


AVG

Syntax:
AVG([DISTINCT | ALL] expression)
returns the average of values

Parameters:

NameDescription
[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:

ExampleSample OutputDescription
SELECT AVG(Amount) FROM Orders
5000
Returns average of Amount column
SELECT AVG(DISTINCT Amount) FROM Orders
4000
Returns average of Amount column and only takes distinct values from Amount column

SUM

Syntax:
SUM([DISTINCT | ALL] expression)
returns the sum of values

Parameters:

NameDescription
[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:

ExampleSample OutputDescription
SELECT SUM(Amount) FROM Orders
5000
Returns sum of Amount column
SELECT SUM(Amount) FROM Orders
4000
Returns sum of Amount column and only takes distinct values from Amount column

COUNT

Syntax:
COUNT([DISTINCT | ALL] expression)
returns the count of values

Parameters:

NameDescription
[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:

ExampleSample OutputDescription
SELECT COUNT(*) FROM Orders
5000
Returns count of all rows (including null and duplicate)
SELECT COUNT(DISTINCT *) FROM Orders
4000
Returns count of all rows (exclude duplicate rows)
SELECT COUNT(DISTINCT OrderId) FROM Orders
3000
Returns count of all rows (exclude duplicate rows and null values of OrderId column)

MIN

Syntax:
MIN(expression)
returns minimum value

Parameters:

NameDescription
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:

ExampleSample OutputDescription
SELECT MIN(Amount) FROM Orders
3000
Returns minimum value for Amount column

MAX

Syntax:
MAX(expression)
returns maximum value

Parameters:

NameDescription
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:

ExampleSample OutputDescription
SELECT MAX(Amount) FROM Orders
5000
Returns maximum value for Amount column

GROUP_CONCAT

Syntax:
GROUP_CONCAT([DISTINCT | ALL]  expression,separator)
returns a string that is the concatenation of all non-NULL values of the input expression separated by the separator

Parameters:

NameDescription
[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:

ExampleSample OutputDescription
SELECT GROUP_CONCAT(Country) FROM Customers
US,India,UK,US
Joins country column from all records from Customers table (Use default separator comma)
SELECT GROUP_CONCAT(DISTINCT Country) FROM Customers
US,India,UK
Joins country column from all records from Customers table and only takes distinct values (Use default separator comma)
SELECT GROUP_CONCAT(Country,';') FROM Customers
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])
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', ...)).

Parameters:

NameDescription
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:

ExampleSample OutputDescription
SELECT 
DATE('now') --e.g. 2021-04-28
,DATE('now','localtime') --e.g. 2021-04-28
,DATE('now','utc') --e.g. 2021-04-28
,DATE('now', 'start of month') --e.g. 2021-04-01
,DATE('now', 'start of month', '+1 month' , '-1 day' ) --e.g. 2021-04-30
,DATE('now', '+5 day') --e.g. 2021-05-03
,DATE('now', '+5 day','+1 month') --e.g. 2021-06-03
,DATE('now', '+5 day','+1 month','+10 year') --e.g. 2031-06-03
,DATE('2020-01-01 22:00', '+2 hour') --e.g. 2020-01-02
--see SQL comment for output--

DATETIME

Syntax:
DATETIME(timestring[,modifier1][,modifier2][,modifier3][,modifier4][,modifier5][,modifier6][,modifier7][,modifier8])
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', ...)).

Parameters:

NameDescription
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:

ExampleSample OutputDescription
SELECT 
DATETIME('now') --e.g. 2021-04-28 04:05:55
,DATETIME('now','+2 hour') --e.g. 2021-04-28 06:05:55
,DATETIME('now','+2 hour','+5 minute') --e.g. 2021-04-28 06:10:55
,DATETIME('now','localtime') --e.g. 2021-04-28 04:05:55
,DATETIME('now','utc') --e.g. 2021-04-28 02:05:55
,DATETIME(1092941466, 'unixepoch') --e.g. 2004-08-19 18:51:06
--see SQL comment for output--

TIME

Syntax:
TIME(timestring[,modifier1][,modifier2][,modifier3][,modifier4][,modifier5][,modifier6][,modifier7][,modifier8])
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', ...)).

Parameters:

NameDescription
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:

ExampleSample OutputDescription
SELECT 
TIME('now') --e.g. 04:15:19
,TIME('now','+2 hour') --e.g. 06:15:19
,TIME('now','+2 hour','+5 minute') --e.g. 06:20:19
,TIME('now','+2 hour','+5 minute','+10 second') --e.g. 06:20:29
--see SQL comment for output--

JULIANDAY

Syntax:
JULIANDAY(timestring[,modifier1][,modifier2][,modifier3][,modifier4][,modifier5][,modifier6][,modifier7][,modifier8])
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', ...)).

Parameters:

NameDescription
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:

ExampleSample OutputDescription
SELECT 
,JULIANDAY('now') --e.g. 2459332.67731214
,JULIANDAY('2016-10-18') --e.g. 2457679.5
,JULIANDAY('2016-10-18 16:45') --e.g. 2457680.19791667
,JULIANDAY('2016-10-18 16:45:30') --e.g. 2457680.19826389
--see SQL comment for output--

STRFTIME

Syntax:
STRFTIME(format,timestring[,modifier1][,modifier2][,modifier3][,modifier4][,modifier5][,modifier6][,modifier7][,modifier8])
This function takes a time string as an argument (and optionally one or more modifiers). Returns date in this format: (specified by format parameter).

Parameters:

NameDescription
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:

ExampleSample OutputDescription
SELECT 
STRFTIME('%Y-%m-%dT%H:%M:%f','now') --e.g. 2021-04-28T04:15:19.000
,STRFTIME('%Y-%m-%dT%H:%M:%f','now','+2 hour') --e.g. 2021-04-28T06:15:19.000
,STRFTIME('%s', '2004-08-19 18:51:06') --e.g. unixepoch 1092941466 and to get date from epoch try DATETIME(1092941466, 'unixepoch')
--see SQL comment for output--

Advanced Functions


NULLIF

Syntax:
NULLIF(expr1,expr2)
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.

Parameters:

NameDescription
expr1[String] first value or expression to check.
expr2[String] second value or expression to check.

Examples:

ExampleSample OutputDescription
SELECT NULLIF('A','B');
A
When both values are not same then first value is returned
SELECT NULLIF('A','A');
(NULL)
When both values same then null is returned
SELECT NULLIF('A',null);
A
When both values same then null is returned

IFNULL

Syntax:
IFNULL(expr1,expr2)
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.

Parameters:

NameDescription
expr1[String] first value or expression to check.
expr2[String] second value or expression to check.

Examples:

ExampleSample OutputDescription
SELECT IFNULL('ABCD','N/A');
ABCD
SELECT IFNULL('ABCD',null);
ABCD
SELECT IFNULL(null,'N/A');
N/A
SELECT IFNULL(null,null);
(NULL)
SELECT IFNULL(UPPER(null),'N/A');
N/A
Returns N/A because first expression produces null

COALESCE

Syntax:
COALESCE(expr1,expr2[,expr3][,expr4][,expr5][,expr6][,expr7][,expr8])
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.

Parameters:

NameDescription
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:

ExampleSample OutputDescription
SELECT COALESCE('A','B','C');
A
Returns A - first non-null value or expression
SELECT COALESCE(1+1,null,3+3);
2
Returns 2 - first non-null value or expression
SELECT COALESCE(null,'B','C');
B
Returns B - first non-null value or expression. Since first arg is null it returns seconds arg