SQLite Viewer - Custom SQL functions

All functions return NULL, if any argument is NULL.

General functions

Function Parameters Description
active_playlist   Returns the index of the active playlist
file_exists '<path>' Returns 1, if <path> exists, 0 otherwise unless <path> is NULL.
format_length <length in seconds> Formats the specified length as weeks, days, hours, minutes and seconds
format_length_hours <length in seconds> Formats the specified length as hours, minutes and seconds
strpos '<string>', '<search>' Search <search> in <string> and return the 1-based position. Returns 0, if <search> is not found.
padl '<string>', <length> [, '<filler>'] Pads <string> to the length <length> with <filler> on the left side. <filler> is optional and defaults to ' '. If <filler> contains more than one character, <filler> will be added as many times as it fits without exceeding <length>.
padr '<string>', <length> [, '<filler>'] Pads <string> to the length <length> with <filler> on the right side. <filler> is optional and defaults to ' '. If <filler> contains more than one character, <filler> will be added as many times as it fits without exceeding <length>.
playing_playlist   Returns the index of the playing playlist
regexp '<regular expression>', '<string>' Returns 1, if <string> matches <regular expression> and 0 otherwise. This function also implements the SQLite REGEXP operator. The supported regular expression syntax is described here.
regexp_replace '<regular expression>', '<format>', '<string>' [,<flag=0>] Returns the parts of <string>, that don't match <regular expression> unchanged and replace the matched parts by <format>. If flag=1 the unmatched parts will be dropped. The supported formats are described here.
regexp_replace_first '<regular expression>', '<format>', '<string>' [,<flag=0>] Works like regexp_replace, but only replaces the first found match.
regexp_search '<regular expression>', '<string>' Returns 1, if a match of <regular expression> inside of <string> is found and 0 otherwise.
strsortgroup '<string>', '<delimiter>' Sorts the groups inside <string> which are separated by <delimiter>.
tf_focus_item
<playlist index>, '<title format expression>' Evaluates <title format expression> for the item which has the focus in the playlist with the index <playlist index>
tf_focus_item_pn '<playlist name>, '<title format expression>' Evaluates <title format expression> for the item which has the focus in the playlist with the name <playlist name>
tf_playing_item
'<title format expression>' Evaluates <title format expression> for the item which is the currently playing item

Auto loaded modules

The following modules are provided and auto-loaded.

Module Description Functions / Virtual tables Reference
csv Provides a virtual table for reading csv files
CREATE VIRTUAL TABLE temp.csv USING csv(filename=FILENAME); SELECT * FROM csv;
SQLite sources
damerau_levenshtein Provides a function for getting the damerau-levenshtein distance for two strings damerau_levenshtein Github
eval Provides the function eval to run SQL statements provided by a string eval SQLite sources
extension_functions Several  functions for various purposes Scalar (math) acos, asin, atan, atn2, atan2, acosh, asinh, atanh, difference, degrees, radians, cos, sin, tan, cot, cosh, sinh, tanh, coth, exp, log, log10, power, sign, sqrt, square, ceil, floor, pi SQLite contributions
Scalar (string) replicate, charindex, leftstr, rightstr, reverse, proper, padc, strfilter
Aggregate stdev, variance, mode, median, lower_quartile, upper_quartile
fileio Provides the possibility to read and write files readfile, writefile SQLite sources
levenshtein Provides a function for getting the levenshtein discance between two strings levenshtein Github
md5 Provides functions for handling md5 hashes Scalar md5, md5file Github
Aggregate group_md5
metaphone Provides a function to get the metaphone value of a string metaphone Github
series Provides the virtual table generate_series to generate series
SELECT * FROM generate_series(0,100);
SQLite sources
totype Provides type conversion functions tointeger, toreal SQLite sources
unicode Provides functions for handling unicode strings upper, lower, title,fold, like, unaccent Github
uuid Provides functions for handling uuids
uuidgen, uuid, xuuid
Github
 
Virtual table functions

The virtual table functions are only valid when querying the virtual tables MediaLibrary and Playlist. Their first argument is always the fixed expression metadb_handle. Due to a limitation of SQLite these functions cannot be used in queries containing a group-by clause or the distinct attribute.

Function Parameters Description
tf metadb_handle, '<title format expression>' Evaluate <title format expression> and return the result