SQLite Interface

Log analysis in lnav can be done using the SQLite interface. Log messages can be accessed via virtual tables that are created for each file format. The tables have the same name as the log format and each message is its own row in the table. For example, given the following log message from an Apache access log:

127.0.0.1 - frank [10/Oct/2000:13:55:36 -0700] "GET /apache_pb.gif HTTP/1.0" 200 2326

These columns would be available for its row in the access_log table:

log_line

log_part

log_time

log_idle_msecs

log_level

log_mark

log_comment

log_tags

log_filters

c_ip

cs_method

cs_referer

cs_uri_query

cs_uri_stem

cs_user_agent

cs_username

cs_version

sc_bytes

sc_status

0

<NULL>

2000-10-10 13:55:36.000

0

info

1

<NULL>

<NULL>

<NULL>

127.0.0.1

GET

<NULL>

<NULL>

/apache_pb.gif

<NULL>

frank

HTTP/1.0

2326

200

Note

Some columns are hidden by default to reduce the amount of noise in results, but they can still be accessed when explicitly used. The hidden columns are: log_path, log_text, and log_body.

You can activate the SQL prompt by pressing the ; key. At the prompt, you can start typing in the desired SQL statement and/or double-tap TAB to activate auto-completion. A help window will appear above the prompt to guide you in the usage of SQL keywords and functions.

_images/sql-help.png

Screenshot of the online help for the SQL prompt.

_images/group_concat-help.png

Screenshot of the online help for the group_concat() function.

A simple query to perform on an Apache access log might be to get the average and maximum number of bytes returned by the server, grouped by IP address:

;SELECT c_ip, avg(sc_bytes), max(sc_bytes) FROM access_log GROUP BY c_ip

After pressing Enter, SQLite will execute the query using lnav’s virtual table implementation to extract the data directly from the log files. Once the query has finished, the main window will switch to the DB view to show the results. Press q to return to the log view and press v to return to the log view. If the SQL results contain a log_line column, you can press to Shift + V to switch between the DB view and the log

_images/query-results.png

Screenshot of the SQL results view.

The DB view has the following display features:

  • Column headers stick to the top of the view when scrolling.

  • A stacked bar chart of the numeric column values is displayed underneath the rows. Pressing TAB will cycle through displaying no columns, each individual column, or all columns.

  • JSON columns in the top row can be pretty-printed by pressing p. The display will show the value and JSON-Pointer path that can be passed to the jget function.

Extensions

To make it easier to analyze log data from within lnav, there are several built-in extensions that provide extra functions and collators beyond those provided by SQLite. The majority of the functions are from the extensions-functions.c file available from the sqlite.org web site.

Tip

You can include a SQLite database file on the command-line and use lnav’s interface to perform queries. The database will be attached with a name based on the database file name.

Commands

A SQL command is an internal macro implemented by lnav.

  • .schema - Open the schema view. This view contains a dump of the schema for the internal tables and any tables in attached databases.

  • .msgformats - Executes a canned query that groups and counts log messages by the format of their message bodies. This command can be useful for quickly finding out the types of messages that are most common in a log file.

Variables

The following variables are available in SQL statements:

  • $LINES - The number of lines in the terminal window.

  • $COLS - The number of columns in the terminal window.

Environment

Environment variables can be accessed in queries using the usual syntax of $VAR_NAME. For example, to read the value of the “USER” variable, you can write:

;SELECT $USER

Collators

  • naturalcase - Compare strings “naturally” so that number values in the string are compared based on their numeric value and not their character values. For example, “foo10” would be considered greater than “foo2”.

  • naturalnocase - The same as naturalcase, but case-insensitive.

  • ipaddress - Compare IPv4/IPv6 addresses.

Reference

The following is a reference of the SQL syntax and functions that are available:

expr [NOT] BETWEEN low AND hi

Test if an expression is between two values.

Parameters
  • low* — The low point

  • hi* — The high point

Examples

To check if 3 is between 5 and 10:

;SELECT 3 BETWEEN 5 AND 10
0

To check if 10 is between 5 and 10:

;SELECT 10 BETWEEN 5 AND 10
1

ATTACH DATABASE filename AS schema-name

Attach a database file to the current connection.

Parameters
  • filename* — The path to the database file.

  • schema-name* — The prefix for tables in this database.

Examples

To attach the database file ‘/tmp/customers.db’ with the name customers:

;ATTACH DATABASE '/tmp/customers.db' AS customers

CREATE [TEMP] VIEW [IF NOT EXISTS] [schema-name.] view-name AS select-stmt

Assign a name to a SELECT statement

Parameters
  • IF NOT EXISTS — Do not create the view if it already exists

  • schema-name. — The database to create the view in

  • view-name* — The name of the view

  • select-stmt* — The SELECT statement the view represents


CREATE [TEMP] TABLE [IF NOT EXISTS] [schema-name.] table-name AS select-stmt

Create a table


WITH RECURSIVE cte-table-name AS select-stmt

Create a temporary view that exists only for the duration of a SQL statement.

Parameters
  • cte-table-name* — The name for the temporary table.

  • select-stmt* — The SELECT statement used to populate the temporary table.


CAST(expr AS type-name)

Convert the value of the given expression to a different storage class specified by type-name.

Parameters
  • expr* — The value to convert.

  • type-name* — The name of the type to convert to.

Examples

To cast the value 1.23 as an integer:

;SELECT CAST(1.23 AS INTEGER)
1

CASE [base-expr] WHEN cmp-expr ELSE [else-expr] END

Evaluate a series of expressions in order until one evaluates to true and then return it’s result. Similar to an IF-THEN-ELSE construct in other languages.

Parameters
  • base-expr — The base expression that is used for comparison in the branches

  • cmp-expr — The expression to test if this branch should be taken

  • else-expr — The result of this CASE if no branches matched.

Examples

To evaluate the number one and return the string ‘one’:

;SELECT CASE 1 WHEN 0 THEN 'zero' WHEN 1 THEN 'one' END
one

expr COLLATE collation-name

Assign a collating sequence to the expression.

Parameters
  • collation-name* — The name of the collator.

Examples

To change the collation method for string comparisons:

;SELECT ('a2' < 'a10'), ('a2' < 'a10' COLLATE naturalnocase)
('a2' < 'a10') ('a2' < 'a10' COLLATE naturalnocase)
             0                                    1

DETACH DATABASE schema-name

Detach a database from the current connection.

Parameters
  • schema-name* — The prefix for tables in this database.

Examples

To detach the database named ‘customers’:

;DETACH DATABASE customers

DELETE FROM table-name WHERE [cond]

Delete rows from a table

Parameters
  • table-name* — The name of the table

  • cond — The conditions used to delete the rows.


DROP INDEX [IF EXISTS] [schema-name.] index-name

Drop an index


DROP TABLE [IF EXISTS] [schema-name.] table-name

Drop a table


DROP VIEW [IF EXISTS] [schema-name.] view-name

Drop a view


DROP TRIGGER [IF EXISTS] [schema-name.] trigger-name

Drop a trigger


expr [NOT] GLOB pattern

Match an expression against a glob pattern.

Parameters
  • pattern* — The glob pattern to match against.

Examples

To check if a value matches the pattern ‘*.log’:

;SELECT 'foobar.log' GLOB '*.log'
1

expr [NOT] LIKE pattern

Match an expression against a text pattern.

Parameters
  • pattern* — The pattern to match against.

Examples

To check if a value matches the pattern ‘Hello, %!’:

;SELECT 'Hello, World!' LIKE 'Hello, %!'
1

expr [NOT] REGEXP pattern

Match an expression against a regular expression.

Parameters
  • pattern* — The regular expression to match against.

Examples

To check if a value matches the pattern ‘file-d+’:

;SELECT 'file-23' REGEXP 'file-\d+'
1

SELECT result-column FROM table WHERE [cond] GROUP BY grouping-expr ORDER BY ordering-term LIMIT limit-expr

Query the database and return zero or more rows of data.

Parameters
  • table — The table(s) to query for data

  • cond — The conditions used to select the rows to return.

  • grouping-expr — The expression to use when grouping rows.

  • ordering-term — The values to use when ordering the result set.

  • limit-expr — The maximum number of rows to return

Examples

To select all of the columns from the table ‘syslog_log’:

;SELECT * FROM syslog_log

INSERT INTO [schema-name.] table-name column-name VALUES expr

Insert rows into a table

Examples

To insert the pair containing ‘MSG’ and ‘HELLO, WORLD!’ into the ‘environ’ table:

;INSERT INTO environ VALUES ('MSG', 'HELLO, WORLD!')

OVER([base-window-name] PARTITION BY expr ORDER BY expr, [frame-spec])

Executes the preceding function over a window

Parameters
  • base-window-name — The name of the window definition

  • expr — The values to use for partitioning

  • expr — The values used to order the rows in the window

  • frame-spec — Determines which output rows are read by an aggregate window function


OVER window-name

Executes the preceding function over a window

Parameters
  • window-name* — The name of the window definition


UPDATE table SET column-name WHERE [cond]

Modify a subset of values in zero or more rows of the given table

Parameters
  • table* — The table to update

  • column-name — The columns in the table to update.

  • cond — The condition used to determine whether a row should be updated.

Examples

To mark the syslog message at line 40:

;UPDATE syslog_log SET log_mark = 1 WHERE log_line = 40

abs(x)

Return the absolute value of the argument

Parameters
  • x* — The number to convert

Examples

To get the absolute value of -1:

;SELECT abs(-1)
1
See Also

acos(num), acosh(num), asin(num), asinh(num), atan2(y, x), atan(num), atanh(num), atn2(y, x), avg(X), ceil(num), degrees(radians), exp(x), floor(num), log10(x), log(x), max(X), min(X), pi(), power(base, exp), radians(degrees), round(num, [digits]), sign(num), square(num), sum(X), total(X)


acos(num)

Returns the arccosine of a number, in radians

Parameters
  • num* — A cosine value that is between -1 and 1

Examples

To get the arccosine of 0.2:

;SELECT acos(0.2)
1.36943840600457
See Also

abs(x), acosh(num), asin(num), asinh(num), atan2(y, x), atan(num), atanh(num), atn2(y, x), avg(X), ceil(num), degrees(radians), exp(x), floor(num), log10(x), log(x), max(X), min(X), pi(), power(base, exp), radians(degrees), round(num, [digits]), sign(num), square(num), sum(X), total(X)


acosh(num)

Returns the hyperbolic arccosine of a number

Parameters
  • num* — A number that is one or more

Examples

To get the hyperbolic arccosine of 1.2:

;SELECT acosh(1.2)
0.622362503714779
See Also

abs(x), acos(num), asin(num), asinh(num), atan2(y, x), atan(num), atanh(num), atn2(y, x), avg(X), ceil(num), degrees(radians), exp(x), floor(num), log10(x), log(x), max(X), min(X), pi(), power(base, exp), radians(degrees), round(num, [digits]), sign(num), square(num), sum(X), total(X)


asin(num)

Returns the arcsine of a number, in radians

Parameters
  • num* — A sine value that is between -1 and 1

Examples

To get the arcsine of 0.2:

;SELECT asin(0.2)
0.201357920790331
See Also

abs(x), acos(num), acosh(num), asinh(num), atan2(y, x), atan(num), atanh(num), atn2(y, x), avg(X), ceil(num), degrees(radians), exp(x), floor(num), log10(x), log(x), max(X), min(X), pi(), power(base, exp), radians(degrees), round(num, [digits]), sign(num), square(num), sum(X), total(X)


asinh(num)

Returns the hyperbolic arcsine of a number

Parameters
  • num* — The number

Examples

To get the hyperbolic arcsine of 0.2:

;SELECT asinh(0.2)
0.198690110349241
See Also

abs(x), acos(num), acosh(num), asin(num), atan2(y, x), atan(num), atanh(num), atn2(y, x), avg(X), ceil(num), degrees(radians), exp(x), floor(num), log10(x), log(x), max(X), min(X), pi(), power(base, exp), radians(degrees), round(num, [digits]), sign(num), square(num), sum(X), total(X)


atan(num)

Returns the arctangent of a number, in radians

Parameters
  • num* — The number

Examples

To get the arctangent of 0.2:

;SELECT atan(0.2)
0.197395559849881
See Also

abs(x), acos(num), acosh(num), asin(num), asinh(num), atan2(y, x), atanh(num), atn2(y, x), avg(X), ceil(num), degrees(radians), exp(x), floor(num), log10(x), log(x), max(X), min(X), pi(), power(base, exp), radians(degrees), round(num, [digits]), sign(num), square(num), sum(X), total(X)


atan2(y, x)

Returns the angle in the plane between the positive X axis and the ray from (0, 0) to the point (x, y)

Parameters
  • y* — The y coordinate of the point

  • x* — The x coordinate of the point

Examples

To get the angle, in degrees, for the point at (5, 5):

;SELECT degrees(atan2(5, 5))
45.0
See Also

abs(x), acos(num), acosh(num), asin(num), asinh(num), atan(num), atanh(num), atn2(y, x), avg(X), ceil(num), degrees(radians), exp(x), floor(num), log10(x), log(x), max(X), min(X), pi(), power(base, exp), radians(degrees), round(num, [digits]), sign(num), square(num), sum(X), total(X)


atanh(num)

Returns the hyperbolic arctangent of a number

Parameters
  • num* — The number

Examples

To get the hyperbolic arctangent of 0.2:

;SELECT atanh(0.2)
0.202732554054082
See Also

abs(x), acos(num), acosh(num), asin(num), asinh(num), atan2(y, x), atan(num), atn2(y, x), avg(X), ceil(num), degrees(radians), exp(x), floor(num), log10(x), log(x), max(X), min(X), pi(), power(base, exp), radians(degrees), round(num, [digits]), sign(num), square(num), sum(X), total(X)


atn2(y, x)

Returns the angle in the plane between the positive X axis and the ray from (0, 0) to the point (x, y)

Parameters
  • y* — The y coordinate of the point

  • x* — The x coordinate of the point

Examples

To get the angle, in degrees, for the point at (5, 5):

;SELECT degrees(atn2(5, 5))
45.0
See Also

abs(x), acos(num), acosh(num), asin(num), asinh(num), atan2(y, x), atan(num), atanh(num), avg(X), ceil(num), degrees(radians), exp(x), floor(num), log10(x), log(x), max(X), min(X), pi(), power(base, exp), radians(degrees), round(num, [digits]), sign(num), square(num), sum(X), total(X)


avg(X)

Returns the average value of all non-NULL numbers within a group.

Parameters
  • X* — The value to compute the average of.

Examples

To get the average of the column ‘ex_duration’ from the table ‘lnav_example_log’:

;SELECT avg(ex_duration) FROM lnav_example_log
4.25

To get the average of the column ‘ex_duration’ from the table ‘lnav_example_log’ when grouped by ‘ex_procname’:

;SELECT ex_procname, avg(ex_duration) FROM lnav_example_log GROUP BY ex_procname
ex_procname avg(ex_duration)
gw                       5.0
hw                       2.0
See Also

abs(x), acos(num), acosh(num), asin(num), asinh(num), atan2(y, x), atan(num), atanh(num), atn2(y, x), ceil(num), degrees(radians), exp(x), floor(num), log10(x), log(x), max(X), min(X), pi(), power(base, exp), radians(degrees), round(num, [digits]), sign(num), square(num), sum(X), total(X)


basename(path)

Extract the base portion of a pathname.

Parameters
  • path* — The path

Examples

To get the base of a plain file name:

;SELECT basename('foobar')
foobar

To get the base of a path:

;SELECT basename('foo/bar')
bar

To get the base of a directory:

;SELECT basename('foo/bar/')
bar

To get the base of an empty string:

;SELECT basename('')
.

To get the base of a Windows path:

;SELECT basename('foo\bar')
bar

To get the base of the root directory:

;SELECT basename('/')
/
See Also

dirname(path), joinpath(path), readlink(path), realpath(path)


ceil(num)

Returns the smallest integer that is not less than the argument

Parameters
  • num* — The number to raise to the ceiling

Examples

To get the ceiling of 1.23:

;SELECT ceil(1.23)
2
See Also

abs(x), acos(num), acosh(num), asin(num), asinh(num), atan2(y, x), atan(num), atanh(num), atn2(y, x), avg(X), degrees(radians), exp(x), floor(num), log10(x), log(x), max(X), min(X), pi(), power(base, exp), radians(degrees), round(num, [digits]), sign(num), square(num), sum(X), total(X)


changes()

The number of database rows that were changed, inserted, or deleted by the most recent statement.


charindex(needle, haystack, [start])

Finds the first occurrence of the needle within the haystack and returns the number of prior characters plus 1, or 0 if Y is nowhere found within X

Parameters
  • needle* — The string to look for in the haystack

  • haystack* — The string to search within

  • start — The one-based index within the haystack to start the search

Examples

To search for the string ‘abc’ within ‘abcabc’ and starting at position 2:

;SELECT charindex('abc', 'abcabc', 2)
4

To search for the string ‘abc’ within ‘abcdef’ and starting at position 2:

;SELECT charindex('abc', 'abcdef', 2)
0
See Also

char(X), endswith(str, suffix), extract(str), group_concat(X, [sep]), group_spooky_hash(str), humanize_file_size(value), instr(haystack, needle), leftstr(str, N), length(str), lower(str), ltrim(str, [chars]), padc(str, len), padl(str, len), padr(str, len), printf(format, X), proper(str), regexp_capture(string, pattern), regexp_match(re, str), regexp_replace(str, re, repl), replace(str, old, replacement), replicate(str, N), reverse(str), rightstr(str, N), rtrim(str, [chars]), sparkline(value, [upper]), sparkline(value, [upper]), spooky_hash(str), startswith(str, prefix), strfilter(source, include), substr(str, start, [size]), trim(str, [chars]), unicode(X), upper(str), xpath(xpath, xmldoc)


coalesce(X, Y)

Returns a copy of its first non-NULL argument, or NULL if all arguments are NULL

Parameters
  • X* — A value to check for NULL-ness

  • Y — A value to check for NULL-ness

Examples

To get the first non-null value from three parameters:

;SELECT coalesce(null, 0, null)
0

count(X)

If the argument is ‘*’, the total number of rows in the group is returned. Otherwise, the number of times the argument is non-NULL.

Parameters
  • X* — The value to count.

Examples

To get the count of the non-NULL rows of ‘lnav_example_log’:

;SELECT count(*) FROM lnav_example_log
4

To get the count of the non-NULL values of ‘log_part’ from ‘lnav_example_log’:

;SELECT count(log_part) FROM lnav_example_log
2

date(timestring, modifier)

Returns the date in this format: YYYY-MM-DD.

Parameters
  • timestring* — The string to convert to a date.

  • modifier — A transformation that is applied to the value to the left.

Examples

To get the date portion of the timestamp ‘2017-01-02T03:04:05’:

;SELECT date('2017-01-02T03:04:05')
2017-01-02

To get the date portion of the timestamp ‘2017-01-02T03:04:05’ plus one day:

;SELECT date('2017-01-02T03:04:05', '+1 day')
2017-01-03

To get the date portion of the epoch timestamp 1491341842:

;SELECT date(1491341842, 'unixepoch')
2017-04-04
See Also

datetime(timestring, modifier), julianday(timestring, modifier), strftime(format, timestring, modifier), time(timestring, modifier), timediff(time1, time2), timeslice(time, slice)


datetime(timestring, modifier)

Returns the date and time in this format: YYYY-MM-DD HH:MM:SS.

Parameters
  • timestring* — The string to convert to a date with time.

  • modifier — A transformation that is applied to the value to the left.

Examples

To get the date and time portion of the timestamp ‘2017-01-02T03:04:05’:

;SELECT datetime('2017-01-02T03:04:05')
2017-01-02 03:04:05

To get the date and time portion of the timestamp ‘2017-01-02T03:04:05’ plus one minute:

;SELECT datetime('2017-01-02T03:04:05', '+1 minute')
2017-01-02 03:05:05

To get the date and time portion of the epoch timestamp 1491341842:

;SELECT datetime(1491341842, 'unixepoch')
2017-04-04 21:37:22
See Also

date(timestring, modifier), julianday(timestring, modifier), strftime(format, timestring, modifier), time(timestring, modifier), timediff(time1, time2), timeslice(time, slice)


degrees(radians)

Converts radians to degrees

Parameters
  • radians* — The radians value to convert to degrees

Examples

To convert PI to degrees:

;SELECT degrees(pi())
180.0
See Also

abs(x), acos(num), acosh(num), asin(num), asinh(num), atan2(y, x), atan(num), atanh(num), atn2(y, x), avg(X), ceil(num), exp(x), floor(num), log10(x), log(x), max(X), min(X), pi(), power(base, exp), radians(degrees), round(num, [digits]), sign(num), square(num), sum(X), total(X)


dirname(path)

Extract the directory portion of a pathname.

Parameters
  • path* — The path

Examples

To get the directory of a relative file path:

;SELECT dirname('foo/bar')
foo

To get the directory of an absolute file path:

;SELECT dirname('/foo/bar')
/foo

To get the directory of a file in the root directory:

;SELECT dirname('/bar')
/

To get the directory of a Windows path:

;SELECT dirname('foo\bar')
foo

To get the directory of an empty path:

;SELECT dirname('')
.
See Also

basename(path), joinpath(path), readlink(path), realpath(path)


endswith(str, suffix)

Test if a string ends with the given suffix

Parameters
  • str* — The string to test

  • suffix* — The suffix to check in the string

Examples

To test if the string ‘notbad.jpg’ ends with ‘.jpg’:

;SELECT endswith('notbad.jpg', '.jpg')
1

To test if the string ‘notbad.png’ starts with ‘.jpg’:

;SELECT endswith('notbad.png', '.jpg')
0
See Also

char(X), charindex(needle, haystack, [start]), extract(str), group_concat(X, [sep]), group_spooky_hash(str), humanize_file_size(value), instr(haystack, needle), leftstr(str, N), length(str), lower(str), ltrim(str, [chars]), padc(str, len), padl(str, len), padr(str, len), printf(format, X), proper(str), regexp_capture(string, pattern), regexp_match(re, str), regexp_replace(str, re, repl), replace(str, old, replacement), replicate(str, N), reverse(str), rightstr(str, N), rtrim(str, [chars]), sparkline(value, [upper]), sparkline(value, [upper]), spooky_hash(str), startswith(str, prefix), strfilter(source, include), substr(str, start, [size]), trim(str, [chars]), unicode(X), upper(str), xpath(xpath, xmldoc)


exp(x)

Returns the value of e raised to the power of x

Parameters
  • x* — The exponent

Examples

To raise e to 2:

;SELECT exp(2)
7.38905609893065
See Also

abs(x), acos(num), acosh(num), asin(num), asinh(num), atan2(y, x), atan(num), atanh(num), atn2(y, x), avg(X), ceil(num), degrees(radians), floor(num), log10(x), log(x), max(X), min(X), pi(), power(base, exp), radians(degrees), round(num, [digits]), sign(num), square(num), sum(X), total(X)


first_value(expr)

Returns the result of evaluating the expression against the first row in the window frame.

Parameters
  • expr* — The expression to execute over the first row

See Also

cume_dist(), dense_rank(), lag(expr, [offset], [default]), last_value(expr), lead(expr, [offset], [default]), nth_value(expr, N), ntile(groups), percent_rank(), rank(), row_number()


floor(num)

Returns the largest integer that is not greater than the argument

Parameters
  • num* — The number to lower to the floor

Examples

To get the floor of 1.23:

;SELECT floor(1.23)
1
See Also

abs(x), acos(num), acosh(num), asin(num), asinh(num), atan2(y, x), atan(num), atanh(num), atn2(y, x), avg(X), ceil(num), degrees(radians), exp(x), log10(x), log(x), max(X), min(X), pi(), power(base, exp), radians(degrees), round(num, [digits]), sign(num), square(num), sum(X), total(X)


gethostbyaddr(hostname)

Get the hostname for the given IP address

Parameters
  • hostname* — The IP address to lookup.

Examples

To get the hostname for the IP ‘127.0.0.1’:

;SELECT gethostbyaddr('127.0.0.1')
localhost
See Also

gethostbyname(hostname)


gethostbyname(hostname)

Get the IP address for the given hostname

Parameters
  • hostname* — The DNS hostname to lookup.

Examples

To get the IP address for ‘localhost’:

;SELECT gethostbyname('localhost')
127.0.0.1
See Also

gethostbyaddr(hostname)


glob(pattern, str)

Match a string against Unix glob pattern

Parameters
  • pattern* — The glob pattern

  • str* — The string to match

Examples

To test if the string ‘abc’ matches the glob ‘a*’:

;SELECT glob('a*', 'abc')
1

group_concat(X, [sep])

Returns a string which is the concatenation of all non-NULL values of X separated by a comma or the given separator.

Parameters
  • X* — The value to concatenate.

  • sep — The separator to place between the values.

Examples

To concatenate the values of the column ‘ex_procname’ from the table ‘lnav_example_log’:

;SELECT group_concat(ex_procname) FROM lnav_example_log
hw,gw,gw,gw

To join the values of the column ‘ex_procname’ using the string ‘, ‘:

;SELECT group_concat(ex_procname, ', ') FROM lnav_example_log
hw, gw, gw, gw

To concatenate the distinct values of the column ‘ex_procname’ from the table ‘lnav_example_log’:

;SELECT group_concat(DISTINCT ex_procname) FROM lnav_example_log
hw,gw
See Also

char(X), charindex(needle, haystack, [start]), endswith(str, suffix), extract(str), group_spooky_hash(str), humanize_file_size(value), instr(haystack, needle), leftstr(str, N), length(str), lower(str), ltrim(str, [chars]), padc(str, len), padl(str, len), padr(str, len), printf(format, X), proper(str), regexp_capture(string, pattern), regexp_match(re, str), regexp_replace(str, re, repl), replace(str, old, replacement), replicate(str, N), reverse(str), rightstr(str, N), rtrim(str, [chars]), sparkline(value, [upper]), sparkline(value, [upper]), spooky_hash(str), startswith(str, prefix), strfilter(source, include), substr(str, start, [size]), trim(str, [chars]), unicode(X), upper(str), xpath(xpath, xmldoc)


hex(X)

Returns a string which is the upper-case hexadecimal rendering of the content of its argument.

Parameters
  • X* — The blob to convert to hexadecimal

Examples

To get the hexadecimal rendering of the string ‘abc’:

;SELECT hex('abc')
616263

ifnull(X, Y)

Returns a copy of its first non-NULL argument, or NULL if both arguments are NULL

Parameters
  • X* — A value to check for NULL-ness

  • Y* — A value to check for NULL-ness

Examples

To get the first non-null value between null and zero:

;SELECT ifnull(null, 0)
0

instr(haystack, needle)

Finds the first occurrence of the needle within the haystack and returns the number of prior characters plus 1, or 0 if the needle was not found

Parameters
  • haystack* — The string to search within

  • needle* — The string to look for in the haystack

Examples

To test get the position of ‘b’ in the string ‘abc’:

;SELECT instr('abc', 'b')
2
See Also

char(X), charindex(needle, haystack, [start]), endswith(str, suffix), extract(str), group_concat(X, [sep]), group_spooky_hash(str), humanize_file_size(value), leftstr(str, N), length(str), lower(str), ltrim(str, [chars]), padc(str, len), padl(str, len), padr(str, len), printf(format, X), proper(str), regexp_capture(string, pattern), regexp_match(re, str), regexp_replace(str, re, repl), replace(str, old, replacement), replicate(str, N), reverse(str), rightstr(str, N), rtrim(str, [chars]), sparkline(value, [upper]), sparkline(value, [upper]), spooky_hash(str), startswith(str, prefix), strfilter(source, include), substr(str, start, [size]), trim(str, [chars]), unicode(X), upper(str), xpath(xpath, xmldoc)


jget(json, ptr, [default])

Get the value from a JSON object using a JSON-Pointer.

Parameters
  • json* — The JSON object to query.

  • ptr* — The JSON-Pointer to lookup in the object.

  • default — The default value if the value was not found

Examples

To get the root of a JSON value:

;SELECT jget('1', '')
1

To get the property named ‘b’ in a JSON object:

;SELECT jget('{ "a": 1, "b": 2 }', '/b')
2

To get the ‘msg’ property and return a default if it does not exist:

;SELECT jget(null, '/msg', 'Hello')
Hello
See Also

json_concat(json, value), json_contains(json, value), json_group_array(value), json_group_object(name, value)


joinpath(path)

Join components of a path together.

Parameters
  • path — One or more path components to join together. If an argument starts with a forward or backward slash, it will be considered an absolute path and any preceding elements will be ignored.

Examples

To join a directory and file name into a relative path:

;SELECT joinpath('foo', 'bar')
foo/bar

To join an empty component with other names into a relative path:

;SELECT joinpath('', 'foo', 'bar')
foo/bar

To create an absolute path with two path components:

;SELECT joinpath('/', 'foo', 'bar')
/foo/bar

To create an absolute path from a path component that starts with a forward slash:

;SELECT joinpath('/', 'foo', '/bar')
/bar
See Also

basename(path), dirname(path), readlink(path), realpath(path)


json_concat(json, value)

Returns an array with the given values concatenated onto the end. If the initial value is null, the result will be an array with the given elements. If the initial value is an array, the result will be an array with the given values at the end. If the initial value is not null or an array, the result will be an array with two elements: the initial value and the given value.

Parameters
  • json* — The initial JSON value.

  • value — The value(s) to add to the end of the array.

Examples

To append the number 4 to null:

;SELECT json_concat(NULL, 4)
[4]

To append 4 and 5 to the array [1, 2, 3]:

;SELECT json_concat('[1, 2, 3]', 4, 5)
[1,2,3,4,5]

To concatenate two arrays together:

;SELECT json_concat('[1, 2, 3]', json('[4, 5]'))
[1,2,3,4,5]
See Also

jget(json, ptr, [default]), json_contains(json, value), json_group_array(value), json_group_object(name, value)


json_contains(json, value)

Check if a JSON value contains the given element.

Parameters
  • json* — The JSON value to query.

  • value* — The value to look for in the first argument

Examples

To test if a JSON array contains the number 4:

;SELECT json_contains('[1, 2, 3]', 4)
0

To test if a JSON array contains the string ‘def’:

;SELECT json_contains('["abc", "def"]', 'def')
1
See Also

jget(json, ptr, [default]), json_concat(json, value), json_group_array(value), json_group_object(name, value)


json_group_array(value)

Collect the given values from a query into a JSON array

Parameters
  • value — The values to append to the array

Examples

To create an array from arguments:

;SELECT json_group_array('one', 2, 3.4)
["one",2,3.3999999999999999112]

To create an array from a column of values:

;SELECT json_group_array(column1) FROM (VALUES (1), (2), (3))
[1,2,3]
See Also

jget(json, ptr, [default]), json_concat(json, value), json_contains(json, value), json_group_object(name, value)


json_group_object(name, value)

Collect the given values from a query into a JSON object

Parameters
  • name* — The property name for the value

  • value — The value to add to the object

Examples

To create an object from arguments:

;SELECT json_group_object('a', 1, 'b', 2)
{"a":1,"b":2}

To create an object from a pair of columns:

;SELECT json_group_object(column1, column2) FROM (VALUES ('a', 1), ('b', 2))
{"a":1,"b":2}
See Also

jget(json, ptr, [default]), json_concat(json, value), json_contains(json, value), json_group_array(value)


julianday(timestring, modifier)

Returns the number of days since noon in Greenwich on November 24, 4714 B.C.

Parameters
  • timestring* — The string to convert to a date with time.

  • modifier — A transformation that is applied to the value to the left.

Examples

To get the julian day from the timestamp ‘2017-01-02T03:04:05’:

;SELECT julianday('2017-01-02T03:04:05')
2457755.62783565

To get the julian day from the timestamp ‘2017-01-02T03:04:05’ plus one minute:

;SELECT julianday('2017-01-02T03:04:05', '+1 minute')
2457755.62853009

To get the julian day from the timestamp 1491341842:

;SELECT julianday(1491341842, 'unixepoch')
2457848.40094907
See Also

date(timestring, modifier), datetime(timestring, modifier), strftime(format, timestring, modifier), time(timestring, modifier), timediff(time1, time2), timeslice(time, slice)


lag(expr, [offset], [default])

Returns the result of evaluating the expression against the previous row in the partition.

Parameters
  • expr* — The expression to execute over the previous row

  • offset — The offset from the current row in the partition

  • default — The default value if the previous row does not exist instead of NULL

See Also

cume_dist(), dense_rank(), first_value(expr), last_value(expr), lead(expr, [offset], [default]), nth_value(expr, N), ntile(groups), percent_rank(), rank(), row_number()


last_insert_rowid()

Returns the ROWID of the last row insert from the database connection which invoked the function


last_value(expr)

Returns the result of evaluating the expression against the last row in the window frame.

Parameters
  • expr* — The expression to execute over the last row

See Also

cume_dist(), dense_rank(), first_value(expr), lag(expr, [offset], [default]), lead(expr, [offset], [default]), nth_value(expr, N), ntile(groups), percent_rank(), rank(), row_number()


lead(expr, [offset], [default])

Returns the result of evaluating the expression against the next row in the partition.

Parameters
  • expr* — The expression to execute over the next row

  • offset — The offset from the current row in the partition

  • default — The default value if the next row does not exist instead of NULL

See Also

cume_dist(), dense_rank(), first_value(expr), lag(expr, [offset], [default]), last_value(expr), nth_value(expr, N), ntile(groups), percent_rank(), rank(), row_number()


leftstr(str, N)

Returns the N leftmost (UTF-8) characters in the given string.

Parameters
  • str* — The string to return subset.

  • N* — The number of characters from the left side of the string to return.

Examples

To get the first character of the string ‘abc’:

;SELECT leftstr('abc', 1)
a

To get the first ten characters of a string, regardless of size:

;SELECT leftstr('abc', 10)
abc
See Also

char(X), charindex(needle, haystack, [start]), endswith(str, suffix), extract(str), group_concat(X, [sep]), group_spooky_hash(str), humanize_file_size(value), instr(haystack, needle), length(str), lower(str), ltrim(str, [chars]), padc(str, len), padl(str, len), padr(str, len), printf(format, X), proper(str), regexp_capture(string, pattern), regexp_match(re, str), regexp_replace(str, re, repl), replace(str, old, replacement), replicate(str, N), reverse(str), rightstr(str, N), rtrim(str, [chars]), sparkline(value, [upper]), sparkline(value, [upper]), spooky_hash(str), startswith(str, prefix), strfilter(source, include), substr(str, start, [size]), trim(str, [chars]), unicode(X), upper(str), xpath(xpath, xmldoc)


like(pattern, str, [escape])

Match a string against a pattern

Parameters
  • pattern* — The pattern to match. A percent symbol (%) will match zero or more characters and an underscore (_) will match a single character.

  • str* — The string to match

  • escape — The escape character that can be used to prefix a literal percent or underscore in the pattern.

Examples

To test if the string ‘aabcc’ contains the letter ‘b’:

;SELECT like('%b%', 'aabcc')
1

To test if the string ‘aab%’ ends with ‘b%’:

;SELECT like('%b:%', 'aab%', ':')
1

likelihood(value, probability)

Provides a hint to the query planner that the first argument is a boolean that is true with the given probability

Parameters
  • value* — The boolean value to return

  • probability* — A floating point constant between 0.0 and 1.0


likely(value)

Short-hand for likelihood(X,0.9375)

Parameters
  • value* — The boolean value to return


load_extension(path, [entry-point])

Loads SQLite extensions out of the given shared library file using the given entry point.

Parameters
  • path* — The path to the shared library containing the extension.


log(x)

Returns the natural logarithm of x

Parameters
  • x* — The number

Examples

To get the natual logarithm of 8:

;SELECT log(8)
2.07944154167984
See Also

abs(x), acos(num), acosh(num), asin(num), asinh(num), atan2(y, x), atan(num), atanh(num), atn2(y, x), avg(X), ceil(num), degrees(radians), exp(x), floor(num), log10(x), max(X), min(X), pi(), power(base, exp), radians(degrees), round(num, [digits]), sign(num), square(num), sum(X), total(X)


log10(x)

Returns the base-10 logarithm of X

Parameters
  • x* — The number

Examples

To get the logarithm of 100:

;SELECT log10(100)
2.0
See Also

abs(x), acos(num), acosh(num), asin(num), asinh(num), atan2(y, x), atan(num), atanh(num), atn2(y, x), avg(X), ceil(num), degrees(radians), exp(x), floor(num), log(x), max(X), min(X), pi(), power(base, exp), radians(degrees), round(num, [digits]), sign(num), square(num), sum(X), total(X)


log_top_datetime()

Return the timestamp of the line at the top of the log view.


log_top_line()

Return the line number at the top of the log view.


ltrim(str, [chars])

Returns a string formed by removing any and all characters that appear in the second argument from the left side of the first.

Parameters
  • str* — The string to trim characters from the left side

  • chars — The characters to trim. Defaults to spaces.

Examples

To trim the leading whitespace from the string ‘ abc’:

;SELECT ltrim('   abc')
abc

To trim the characters ‘a’ or ‘b’ from the left side of the string ‘aaaabbbc’:

;SELECT ltrim('aaaabbbc', 'ab')
c
See Also

char(X), charindex(needle, haystack, [start]), endswith(str, suffix), extract(str), group_concat(X, [sep]), group_spooky_hash(str), humanize_file_size(value), instr(haystack, needle), leftstr(str, N), length(str), lower(str), padc(str, len), padl(str, len), padr(str, len), printf(format, X), proper(str), regexp_capture(string, pattern), regexp_match(re, str), regexp_replace(str, re, repl), replace(str, old, replacement), replicate(str, N), reverse(str), rightstr(str, N), rtrim(str, [chars]), sparkline(value, [upper]), sparkline(value, [upper]), spooky_hash(str), startswith(str, prefix), strfilter(source, include), substr(str, start, [size]), trim(str, [chars]), unicode(X), upper(str), xpath(xpath, xmldoc)


max(X)

Returns the argument with the maximum value, or return NULL if any argument is NULL.

Parameters
  • X — The numbers to find the maximum of. If only one argument is given, this function operates as an aggregate.

Examples

To get the largest value from the parameters:

;SELECT max(2, 1, 3)
3

To get the largest value from an aggregate:

;SELECT max(status) FROM http_status_codes
511
See Also

abs(x), acos(num), acosh(num), asin(num), asinh(num), atan2(y, x), atan(num), atanh(num), atn2(y, x), avg(X), ceil(num), degrees(radians), exp(x), floor(num), log10(x), log(x), min(X), pi(), power(base, exp), radians(degrees), round(num, [digits]), sign(num), square(num), sum(X), total(X)


min(X)

Returns the argument with the minimum value, or return NULL if any argument is NULL.

Parameters
  • X — The numbers to find the minimum of. If only one argument is given, this function operates as an aggregate.

Examples

To get the smallest value from the parameters:

;SELECT min(2, 1, 3)
1

To get the smallest value from an aggregate:

;SELECT min(status) FROM http_status_codes
100
See Also

abs(x), acos(num), acosh(num), asin(num), asinh(num), atan2(y, x), atan(num), atanh(num), atn2(y, x), avg(X), ceil(num), degrees(radians), exp(x), floor(num), log10(x), log(x), max(X), pi(), power(base, exp), radians(degrees), round(num, [digits]), sign(num), square(num), sum(X), total(X)


nth_value(expr, N)

Returns the result of evaluating the expression against the nth row in the window frame.

Parameters
  • expr* — The expression to execute over the nth row

  • N* — The row number

See Also

cume_dist(), dense_rank(), first_value(expr), lag(expr, [offset], [default]), last_value(expr), lead(expr, [offset], [default]), ntile(groups), percent_rank(), rank(), row_number()


ntile(groups)

Returns the number of the group that the current row is a part of

Parameters
  • groups* — The number of groups

See Also

cume_dist(), dense_rank(), first_value(expr), lag(expr, [offset], [default]), last_value(expr), lead(expr, [offset], [default]), nth_value(expr, N), percent_rank(), rank(), row_number()


nullif(X, Y)

Returns its first argument if the arguments are different and NULL if the arguments are the same.

Parameters
  • X* — The first argument to compare.

  • Y* — The argument to compare against the first.

Examples

To test if 1 is different from 1:

;SELECT nullif(1, 1)
<NULL>

To test if 1 is different from 2:

;SELECT nullif(1, 2)
1

padc(str, len)

Pad the given string with enough spaces to make it centered within the given length

Parameters
  • str* — The string to pad

  • len* — The minimum desired length of the output string

Examples

To pad the string ‘abc’ to a length of six characters:

;SELECT padc('abc', 6) || 'def'
 abc  def

To pad the string ‘abcdef’ to a length of eight characters:

;SELECT padc('abcdef', 8) || 'ghi'
 abcdef ghi
See Also

char(X), charindex(needle, haystack, [start]), endswith(str, suffix), extract(str), group_concat(X, [sep]), group_spooky_hash(str), humanize_file_size(value), instr(haystack, needle), leftstr(str, N), length(str), lower(str), ltrim(str, [chars]), padl(str, len), padr(str, len), printf(format, X), proper(str), regexp_capture(string, pattern), regexp_match(re, str), regexp_replace(str, re, repl), replace(str, old, replacement), replicate(str, N), reverse(str), rightstr(str, N), rtrim(str, [chars]), sparkline(value, [upper]), sparkline(value, [upper]), spooky_hash(str), startswith(str, prefix), strfilter(source, include), substr(str, start, [size]), trim(str, [chars]), unicode(X), upper(str), xpath(xpath, xmldoc)


padl(str, len)

Pad the given string with leading spaces until it reaches the desired length

Parameters
  • str* — The string to pad

  • len* — The minimum desired length of the output string

Examples

To pad the string ‘abc’ to a length of six characters:

;SELECT padl('abc', 6)
   abc

To pad the string ‘abcdef’ to a length of four characters:

;SELECT padl('abcdef', 4)
abcdef
See Also

char(X), charindex(needle, haystack, [start]), endswith(str, suffix), extract(str), group_concat(X, [sep]), group_spooky_hash(str), humanize_file_size(value), instr(haystack, needle), leftstr(str, N), length(str), lower(str), ltrim(str, [chars]), padc(str, len), padr(str, len), printf(format, X), proper(str), regexp_capture(string, pattern), regexp_match(re, str), regexp_replace(str, re, repl), replace(str, old, replacement), replicate(str, N), reverse(str), rightstr(str, N), rtrim(str, [chars]), sparkline(value, [upper]), sparkline(value, [upper]), spooky_hash(str), startswith(str, prefix), strfilter(source, include), substr(str, start, [size]), trim(str, [chars]), unicode(X), upper(str), xpath(xpath, xmldoc)


padr(str, len)

Pad the given string with trailing spaces until it reaches the desired length

Parameters
  • str* — The string to pad

  • len* — The minimum desired length of the output string

Examples

To pad the string ‘abc’ to a length of six characters:

;SELECT padr('abc', 6) || 'def'
abc   def

To pad the string ‘abcdef’ to a length of four characters:

;SELECT padr('abcdef', 4) || 'ghi'
abcdefghi
See Also

char(X), charindex(needle, haystack, [start]), endswith(str, suffix), extract(str), group_concat(X, [sep]), group_spooky_hash(str), humanize_file_size(value), instr(haystack, needle), leftstr(str, N), length(str), lower(str), ltrim(str, [chars]), padc(str, len), padl(str, len), printf(format, X), proper(str), regexp_capture(string, pattern), regexp_match(re, str), regexp_replace(str, re, repl), replace(str, old, replacement), replicate(str, N), reverse(str), rightstr(str, N), rtrim(str, [chars]), sparkline(value, [upper]), sparkline(value, [upper]), spooky_hash(str), startswith(str, prefix), strfilter(source, include), substr(str, start, [size]), trim(str, [chars]), unicode(X), upper(str), xpath(xpath, xmldoc)


power(base, exp)

Returns the base to the given exponent

Parameters
  • base* — The base number

  • exp* — The exponent

Examples

To raise two to the power of three:

;SELECT power(2, 3)
8.0
See Also

abs(x), acos(num), acosh(num), asin(num), asinh(num), atan2(y, x), atan(num), atanh(num), atn2(y, x), avg(X), ceil(num), degrees(radians), exp(x), floor(num), log10(x), log(x), max(X), min(X), pi(), radians(degrees), round(num, [digits]), sign(num), square(num), sum(X), total(X)


printf(format, X)

Returns a string with this functions arguments substituted into the given format. Substitution points are specified using percent (%) options, much like the standard C printf() function.

Parameters
  • format* — The format of the string to return.

  • X* — The argument to substitute at a given position in the format.

Examples

To substitute ‘World’ into the string ‘Hello, %s!’:

;SELECT printf('Hello, %s!', 'World')
Hello, World!

To right-align ‘small’ in the string ‘align:’ with a column width of 10:

;SELECT printf('align: % 10s', 'small')
align:      small

To format 11 with a width of five characters and leading zeroes:

;SELECT printf('value: %05d', 11)
value: 00011
See Also

char(X), charindex(needle, haystack, [start]), endswith(str, suffix), extract(str), group_concat(X, [sep]), group_spooky_hash(str), humanize_file_size(value), instr(haystack, needle), leftstr(str, N), length(str), lower(str), ltrim(str, [chars]), padc(str, len), padl(str, len), padr(str, len), proper(str), regexp_capture(string, pattern), regexp_match(re, str), regexp_replace(str, re, repl), replace(str, old, replacement), replicate(str, N), reverse(str), rightstr(str, N), rtrim(str, [chars]), sparkline(value, [upper]), sparkline(value, [upper]), spooky_hash(str), startswith(str, prefix), strfilter(source, include), substr(str, start, [size]), trim(str, [chars]), unicode(X), upper(str), xpath(xpath, xmldoc)


quote(X)

Returns the text of an SQL literal which is the value of its argument suitable for inclusion into an SQL statement.

Parameters
  • X* — The string to quote.

Examples

To quote the string ‘abc’:

;SELECT quote('abc')
'abc'

To quote the string ‘abc’123’:

;SELECT quote('abc''123')
'abc''123'

radians(degrees)

Converts degrees to radians

Parameters
  • degrees* — The degrees value to convert to radians

Examples

To convert 180 degrees to radians:

;SELECT radians(180)
3.14159265358979
See Also

abs(x), acos(num), acosh(num), asin(num), asinh(num), atan2(y, x), atan(num), atanh(num), atn2(y, x), avg(X), ceil(num), degrees(radians), exp(x), floor(num), log10(x), log(x), max(X), min(X), pi(), power(base, exp), round(num, [digits]), sign(num), square(num), sum(X), total(X)


raise_error(msg)

Raises an error with the given message when executed

Parameters
  • msg* — The error message


random()

Returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807.


randomblob(N)

Return an N-byte blob containing pseudo-random bytes.

Parameters
  • N* — The size of the blob in bytes.


realpath(path)

Returns the resolved version of the given path, expanding symbolic links and resolving ‘.’ and ‘..’ references.

Parameters
  • path* — The path to resolve.

See Also

basename(path), dirname(path), joinpath(path), readlink(path)


regexp(re, str)

Test if a string matches a regular expression

Parameters
  • re* — The regular expression to use

  • str* — The string to test against the regular expression


regexp_capture(string, pattern)

A table-valued function that executes a regular-expression over a string and returns the captured values. If the regex only matches a subset of the input string, it will be rerun on the remaining parts of the string until no more matches are found.

Parameters
  • string* — The string to match against the given pattern.

  • pattern* — The regular expression to match.

Examples

To extract the key/value pairs ‘a’/1 and ‘b’/2 from the string ‘a=1; b=2’:

;SELECT * FROM regexp_capture('a=1; b=2', '(\w+)=(\d+)')
match_index capture_index capture_name capture_count range_start range_stop content
          0             0 <NULL>                   3           1          4 a=1
          0             1                          3           1          2 a
          0             2                          3           3          4 1
          1             0 <NULL>                   3           6          9 b=2
          1             1                          3           6          7 b
          1             2                          3           8          9 2
See Also

char(X), charindex(needle, haystack, [start]), endswith(str, suffix), extract(str), group_concat(X, [sep]), group_spooky_hash(str), humanize_file_size(value), instr(haystack, needle), leftstr(str, N), length(str), lower(str), ltrim(str, [chars]), padc(str, len), padl(str, len), padr(str, len), printf(format, X), proper(str), regexp_match(re, str), regexp_replace(str, re, repl), replace(str, old, replacement), replicate(str, N), reverse(str), rightstr(str, N), rtrim(str, [chars]), sparkline(value, [upper]), sparkline(value, [upper]), spooky_hash(str), startswith(str, prefix), strfilter(source, include), substr(str, start, [size]), trim(str, [chars]), unicode(X), upper(str), xpath(xpath, xmldoc)


regexp_match(re, str)

Match a string against a regular expression and return the capture groups as JSON.

Parameters
  • re* — The regular expression to use

  • str* — The string to test against the regular expression

Examples

To capture the digits from the string ‘123’:

;SELECT regexp_match('(\d+)', '123')
123

To capture a number and word into a JSON object with the properties ‘col_0’ and ‘col_1’:

;SELECT regexp_match('(\d+) (\w+)', '123 four')
{"col_0":123,"col_1":"four"}

To capture a number and word into a JSON object with the named properties ‘num’ and ‘str’:

;SELECT regexp_match('(?<num>\d+) (?<str>\w+)', '123 four')
{"num":123,"str":"four"}
See Also

char(X), charindex(needle, haystack, [start]), endswith(str, suffix), extract(str), group_concat(X, [sep]), group_spooky_hash(str), humanize_file_size(value), instr(haystack, needle), leftstr(str, N), length(str), lower(str), ltrim(str, [chars]), padc(str, len), padl(str, len), padr(str, len), printf(format, X), proper(str), regexp_capture(string, pattern), regexp_replace(str, re, repl), regexp_replace(str, re, repl), replace(str, old, replacement), replicate(str, N), reverse(str), rightstr(str, N), rtrim(str, [chars]), sparkline(value, [upper]), sparkline(value, [upper]), spooky_hash(str), startswith(str, prefix), strfilter(source, include), substr(str, start, [size]), trim(str, [chars]), unicode(X), upper(str), xpath(xpath, xmldoc)


regexp_replace(str, re, repl)

Replace the parts of a string that match a regular expression.

Parameters
  • str* — The string to perform replacements on

  • re* — The regular expression to match

  • repl* — The replacement string. You can reference capture groups with a backslash followed by the number of the group, starting with 1.

Examples

To replace the word at the start of the string ‘Hello, World!’ with ‘Goodbye’:

;SELECT regexp_replace('Hello, World!', '^(\w+)', 'Goodbye')
Goodbye, World!

To wrap alphanumeric words with angle brackets:

;SELECT regexp_replace('123 abc', '(\w+)', '<\1>')
<123> <abc>
See Also

char(X), charindex(needle, haystack, [start]), endswith(str, suffix), extract(str), group_concat(X, [sep]), group_spooky_hash(str), humanize_file_size(value), instr(haystack, needle), leftstr(str, N), length(str), lower(str), ltrim(str, [chars]), padc(str, len), padl(str, len), padr(str, len), printf(format, X), proper(str), regexp_capture(string, pattern), regexp_match(re, str), regexp_match(re, str), replace(str, old, replacement), replicate(str, N), reverse(str), rightstr(str, N), rtrim(str, [chars]), sparkline(value, [upper]), sparkline(value, [upper]), spooky_hash(str), startswith(str, prefix), strfilter(source, include), substr(str, start, [size]), trim(str, [chars]), unicode(X), upper(str), xpath(xpath, xmldoc)


replace(str, old, replacement)

Returns a string formed by substituting the replacement string for every occurrence of the old string in the given string.

Parameters
  • str* — The string to perform substitutions on.

  • old* — The string to be replaced.

  • replacement* — The string to replace any occurrences of the old string with.

Examples

To replace the string ‘x’ with ‘z’ in ‘abc’:

;SELECT replace('abc', 'x', 'z')
abc

To replace the string ‘a’ with ‘z’ in ‘abc’:

;SELECT replace('abc', 'a', 'z')
zbc
See Also

char(X), charindex(needle, haystack, [start]), endswith(str, suffix), extract(str), group_concat(X, [sep]), group_spooky_hash(str), humanize_file_size(value), instr(haystack, needle), leftstr(str, N), length(str), lower(str), ltrim(str, [chars]), padc(str, len), padl(str, len), padr(str, len), printf(format, X), proper(str), regexp_capture(string, pattern), regexp_match(re, str), regexp_replace(str, re, repl), replicate(str, N), reverse(str), rightstr(str, N), rtrim(str, [chars]), sparkline(value, [upper]), sparkline(value, [upper]), spooky_hash(str), startswith(str, prefix), strfilter(source, include), substr(str, start, [size]), trim(str, [chars]), unicode(X), upper(str), xpath(xpath, xmldoc)


rightstr(str, N)

Returns the N rightmost (UTF-8) characters in the given string.

Parameters
  • str* — The string to return subset.

  • N* — The number of characters from the right side of the string to return.

Examples

To get the last character of the string ‘abc’:

;SELECT rightstr('abc', 1)
c

To get the last ten characters of a string, regardless of size:

;SELECT rightstr('abc', 10)
abc
See Also

char(X), charindex(needle, haystack, [start]), endswith(str, suffix), extract(str), group_concat(X, [sep]), group_spooky_hash(str), humanize_file_size(value), instr(haystack, needle), leftstr(str, N), length(str), lower(str), ltrim(str, [chars]), padc(str, len), padl(str, len), padr(str, len), printf(format, X), proper(str), regexp_capture(string, pattern), regexp_match(re, str), regexp_replace(str, re, repl), replace(str, old, replacement), replicate(str, N), reverse(str), rtrim(str, [chars]), sparkline(value, [upper]), sparkline(value, [upper]), spooky_hash(str), startswith(str, prefix), strfilter(source, include), substr(str, start, [size]), trim(str, [chars]), unicode(X), upper(str), xpath(xpath, xmldoc)


round(num, [digits])

Returns a floating-point value rounded to the given number of digits to the right of the decimal point.

Parameters
  • num* — The value to round.

  • digits — The number of digits to the right of the decimal to round to.

Examples

To round the number 123.456 to an integer:

;SELECT round(123.456)
123.0

To round the number 123.456 to a precision of 1:

;SELECT round(123.456, 1)
123.5

To round the number 123.456 to a precision of 5:

;SELECT round(123.456, 5)
123.456
See Also

abs(x), acos(num), acosh(num), asin(num), asinh(num), atan2(y, x), atan(num), atanh(num), atn2(y, x), avg(X), ceil(num), degrees(radians), exp(x), floor(num), log10(x), log(x), max(X), min(X), pi(), power(base, exp), radians(degrees), sign(num), square(num), sum(X), total(X)


row_number()

Returns the number of the row within the current partition, starting from 1.

Examples

To number messages from a process:

;SELECT row_number() OVER (PARTITION BY ex_procname ORDER BY log_line) AS msg_num, ex_procname, log_body FROM lnav_example_log
msg_num ex_procname    log_body
      1 gw          Goodbye, World!
      2 gw          Goodbye, World!
      3 gw          Goodbye, World!
      1 hw          Hello, World!
See Also

cume_dist(), dense_rank(), first_value(expr), lag(expr, [offset], [default]), last_value(expr), lead(expr, [offset], [default]), nth_value(expr, N), ntile(groups), percent_rank(), rank()


rtrim(str, [chars])

Returns a string formed by removing any and all characters that appear in the second argument from the right side of the first.

Parameters
  • str* — The string to trim characters from the right side

  • chars — The characters to trim. Defaults to spaces.

Examples

To trim the whitespace from the end of the string ‘abc ‘:

;SELECT rtrim('abc   ')
abc

To trim the characters ‘b’ and ‘c’ from the string ‘abbbbcccc’:

;SELECT rtrim('abbbbcccc', 'bc')
a
See Also

char(X), charindex(needle, haystack, [start]), endswith(str, suffix), extract(str), group_concat(X, [sep]), group_spooky_hash(str), humanize_file_size(value), instr(haystack, needle), leftstr(str, N), length(str), lower(str), ltrim(str, [chars]), padc(str, len), padl(str, len), padr(str, len), printf(format, X), proper(str), regexp_capture(string, pattern), regexp_match(re, str), regexp_replace(str, re, repl), replace(str, old, replacement), replicate(str, N), reverse(str), rightstr(str, N), sparkline(value, [upper]), sparkline(value, [upper]), spooky_hash(str), startswith(str, prefix), strfilter(source, include), substr(str, start, [size]), trim(str, [chars]), unicode(X), upper(str), xpath(xpath, xmldoc)


sign(num)

Returns the sign of the given number as -1, 0, or 1

Parameters
  • num* — The number

Examples

To get the sign of 10:

;SELECT sign(10)
1

To get the sign of 0:

;SELECT sign(0)
0

To get the sign of -10:

;SELECT sign(-10)
-1
See Also

abs(x), acos(num), acosh(num), asin(num), asinh(num), atan2(y, x), atan(num), atanh(num), atn2(y, x), avg(X), ceil(num), degrees(radians), exp(x), floor(num), log10(x), log(x), max(X), min(X), pi(), power(base, exp), radians(degrees), round(num, [digits]), square(num), sum(X), total(X)


sparkline(value, [upper])

An aggregate function to convert numeric values to a sparkline bar chart

Parameters
  • value* — The numeric values to chart

  • upper — The upper bound of the numeric range. If not provided, the default is derived from all of the provided values

Examples

To chart the values in a JSON array:

;SELECT sparkline(value) FROM json_each('[0, 1, 2, 3, 4, 5, 6, 7, 8]')
 ▁▂▃▄▅▆▇█
See Also

char(X), charindex(needle, haystack, [start]), endswith(str, suffix), extract(str), group_concat(X, [sep]), group_spooky_hash(str), humanize_file_size(value), instr(haystack, needle), leftstr(str, N), length(str), lower(str), ltrim(str, [chars]), padc(str, len), padl(str, len), padr(str, len), printf(format, X), proper(str), regexp_capture(string, pattern), regexp_match(re, str), regexp_replace(str, re, repl), replace(str, old, replacement), replicate(str, N), reverse(str), rightstr(str, N), rtrim(str, [chars]), sparkline(value, [upper]), spooky_hash(str), startswith(str, prefix), strfilter(source, include), substr(str, start, [size]), trim(str, [chars]), unicode(X), upper(str), xpath(xpath, xmldoc)


spooky_hash(str)

Compute the hash value for the given arguments.

Parameters
  • str — The string to hash

Examples

To produce a hash for the string ‘Hello, World!’:

;SELECT spooky_hash('Hello, World!')
0b1d52cc5427db4c6a9eed9d3e5700f4

To produce a hash for the parameters where one is NULL:

;SELECT spooky_hash('Hello, World!', NULL)
c96ee75d48e6ea444fee8af948f6da25

To produce a hash for the parameters where one is an empty string:

;SELECT spooky_hash('Hello, World!', '')
c96ee75d48e6ea444fee8af948f6da25

To produce a hash for the parameters where one is a number:

;SELECT spooky_hash('Hello, World!', 123)
f96b3d9c1a19f4394c97a1b79b1880df
See Also

char(X), charindex(needle, haystack, [start]), endswith(str, suffix), extract(str), group_concat(X, [sep]), group_spooky_hash(str), humanize_file_size(value), instr(haystack, needle), leftstr(str, N), length(str), lower(str), ltrim(str, [chars]), padc(str, len), padl(str, len), padr(str, len), printf(format, X), proper(str), regexp_capture(string, pattern), regexp_match(re, str), regexp_replace(str, re, repl), replace(str, old, replacement), replicate(str, N), reverse(str), rightstr(str, N), rtrim(str, [chars]), sparkline(value, [upper]), sparkline(value, [upper]), startswith(str, prefix), strfilter(source, include), substr(str, start, [size]), trim(str, [chars]), unicode(X), upper(str), xpath(xpath, xmldoc)


sqlite_compileoption_get(N)

Returns the N-th compile-time option used to build SQLite or NULL if N is out of range.

Parameters
  • N* — The option number to get


sqlite_compileoption_used(option)

Returns true (1) or false (0) depending on whether or not that compile-time option was used during the build.

Parameters
  • option* — The name of the compile-time option.

Examples

To check if the SQLite library was compiled with ENABLE_FTS3:

;SELECT sqlite_compileoption_used('ENABLE_FTS3')
1

sqlite_source_id()

Returns a string that identifies the specific version of the source code that was used to build the SQLite library.


sqlite_version()

Returns the version string for the SQLite library that is running.


square(num)

Returns the square of the argument

Parameters
  • num* — The number to square

Examples

To get the square of two:

;SELECT square(2)
4
See Also

abs(x), acos(num), acosh(num), asin(num), asinh(num), atan2(y, x), atan(num), atanh(num), atn2(y, x), avg(X), ceil(num), degrees(radians), exp(x), floor(num), log10(x), log(x), max(X), min(X), pi(), power(base, exp), radians(degrees), round(num, [digits]), sign(num), sum(X), total(X)


startswith(str, prefix)

Test if a string begins with the given prefix

Parameters
  • str* — The string to test

  • prefix* — The prefix to check in the string

Examples

To test if the string ‘foobar’ starts with ‘foo’:

;SELECT startswith('foobar', 'foo')
1

To test if the string ‘foobar’ starts with ‘bar’:

;SELECT startswith('foobar', 'bar')
0
See Also

char(X), charindex(needle, haystack, [start]), endswith(str, suffix), extract(str), group_concat(X, [sep]), group_spooky_hash(str), humanize_file_size(value), instr(haystack, needle), leftstr(str, N), length(str), lower(str), ltrim(str, [chars]), padc(str, len), padl(str, len), padr(str, len), printf(format, X), proper(str), regexp_capture(string, pattern), regexp_match(re, str), regexp_replace(str, re, repl), replace(str, old, replacement), replicate(str, N), reverse(str), rightstr(str, N), rtrim(str, [chars]), sparkline(value, [upper]), sparkline(value, [upper]), spooky_hash(str), strfilter(source, include), substr(str, start, [size]), trim(str, [chars]), unicode(X), upper(str), xpath(xpath, xmldoc)


strftime(format, timestring, modifier)

Returns the date formatted according to the format string specified as the first argument.

Parameters
  • format* — A format string with substitutions similar to those found in the strftime() standard C library.

  • timestring* — The string to convert to a date with time.

  • modifier — A transformation that is applied to the value to the left.

Examples

To get the year from the timestamp ‘2017-01-02T03:04:05’:

;SELECT strftime('%Y', '2017-01-02T03:04:05')
2017

To create a string with the time from the timestamp ‘2017-01-02T03:04:05’ plus one minute:

;SELECT strftime('The time is: %H:%M:%S', '2017-01-02T03:04:05', '+1 minute')
The time is: 03:05:05

To create a string with the Julian day from the epoch timestamp 1491341842:

;SELECT strftime('Julian day: %J', 1491341842, 'unixepoch')
Julian day: 2457848.400949074
See Also

date(timestring, modifier), datetime(timestring, modifier), julianday(timestring, modifier), time(timestring, modifier), timediff(time1, time2), timeslice(time, slice)


substr(str, start, [size])

Returns a substring of input string X that begins with the Y-th character and which is Z characters long.

Parameters
  • str* — The string to extract a substring from.

  • start* — The index within ‘str’ that is the start of the substring. Indexes begin at 1. A negative value means that the substring is found by counting from the right rather than the left.

  • size — The size of the substring. If not given, then all characters through the end of the string are returned. If the value is negative, then the characters before the start are returned.

Examples

To get the substring starting at the second character until the end of the string ‘abc’:

;SELECT substr('abc', 2)
bc

To get the substring of size one starting at the second character of the string ‘abc’:

;SELECT substr('abc', 2, 1)
b

To get the substring starting at the last character until the end of the string ‘abc’:

;SELECT substr('abc', -1)
c

To get the substring starting at the last character and going backwards one step of the string ‘abc’:

;SELECT substr('abc', -1, -1)
b
See Also

char(X), charindex(needle, haystack, [start]), endswith(str, suffix), extract(str), group_concat(X, [sep]), group_spooky_hash(str), humanize_file_size(value), instr(haystack, needle), leftstr(str, N), length(str), lower(str), ltrim(str, [chars]), padc(str, len), padl(str, len), padr(str, len), printf(format, X), proper(str), regexp_capture(string, pattern), regexp_match(re, str), regexp_replace(str, re, repl), replace(str, old, replacement), replicate(str, N), reverse(str), rightstr(str, N), rtrim(str, [chars]), sparkline(value, [upper]), sparkline(value, [upper]), spooky_hash(str), startswith(str, prefix), strfilter(source, include), trim(str, [chars]), unicode(X), upper(str), xpath(xpath, xmldoc)


sum(X)

Returns the sum of the values in the group as an integer.

Parameters
  • X* — The values to add.

Examples

To sum all of the values in the column ‘ex_duration’ from the table ‘lnav_example_log’:

;SELECT sum(ex_duration) FROM lnav_example_log
17
See Also

abs(x), acos(num), acosh(num), asin(num), asinh(num), atan2(y, x), atan(num), atanh(num), atn2(y, x), avg(X), ceil(num), degrees(radians), exp(x), floor(num), log10(x), log(x), max(X), min(X), pi(), power(base, exp), radians(degrees), round(num, [digits]), sign(num), square(num), total(X)


time(timestring, modifier)

Returns the time in this format: HH:MM:SS.

Parameters
  • timestring* — The string to convert to a time.

  • modifier — A transformation that is applied to the value to the left.

Examples

To get the time portion of the timestamp ‘2017-01-02T03:04:05’:

;SELECT time('2017-01-02T03:04:05')
03:04:05

To get the time portion of the timestamp ‘2017-01-02T03:04:05’ plus one minute:

;SELECT time('2017-01-02T03:04:05', '+1 minute')
03:05:05

To get the time portion of the epoch timestamp 1491341842:

;SELECT time(1491341842, 'unixepoch')
21:37:22
See Also

date(timestring, modifier), datetime(timestring, modifier), julianday(timestring, modifier), strftime(format, timestring, modifier), timediff(time1, time2), timeslice(time, slice)


timediff(time1, time2)

Compute the difference between two timestamps in seconds

Parameters
  • time1* — The first timestamp

  • time2* — The timestamp to subtract from the first

Examples

To get the difference between two timestamps:

;SELECT timediff('2017-02-03T04:05:06', '2017-02-03T04:05:00')
6.0

To get the difference between relative timestamps:

;SELECT timediff('today', 'yesterday')
86400.0
See Also

date(timestring, modifier), datetime(timestring, modifier), julianday(timestring, modifier), strftime(format, timestring, modifier), time(timestring, modifier), timeslice(time, slice)


timeslice(time, slice)

Return the start of the slice of time that the given timestamp falls in.

Parameters
  • time* — The timestamp to get the time slice for.

  • slice* — The size of the time slices

Examples

To get the timestamp rounded down to the start of the ten minute slice:

;SELECT timeslice('2017-01-01T05:05:00', '10m')
2017-01-01 05:00:00.000

To group log messages into five minute buckets and count them:

;SELECT timeslice(log_time_msecs, '5m') AS slice, count(*) FROM lnav_example_log GROUP BY slice
2017-01-01 05:00:00.000
See Also

date(timestring, modifier), datetime(timestring, modifier), julianday(timestring, modifier), strftime(format, timestring, modifier), time(timestring, modifier), timediff(time1, time2)


total(X)

Returns the sum of the values in the group as a floating-point.

Parameters
  • X* — The values to add.

Examples

To total all of the values in the column ‘ex_duration’ from the table ‘lnav_example_log’:

;SELECT total(ex_duration) FROM lnav_example_log
17.0
See Also

abs(x), acos(num), acosh(num), asin(num), asinh(num), atan2(y, x), atan(num), atanh(num), atn2(y, x), avg(X), ceil(num), degrees(radians), exp(x), floor(num), log10(x), log(x), max(X), min(X), pi(), power(base, exp), radians(degrees), round(num, [digits]), sign(num), square(num), sum(X)


total_changes()

Returns the number of row changes caused by INSERT, UPDATE or DELETE statements since the current database connection was opened.


trim(str, [chars])

Returns a string formed by removing any and all characters that appear in the second argument from the left and right sides of the first.

Parameters
  • str* — The string to trim characters from the left and right sides.

  • chars — The characters to trim. Defaults to spaces.

Examples

To trim whitespace from the start and end of the string ‘ abc ‘:

;SELECT trim('    abc   ')
abc

To trim the characters ‘-‘ and ‘+’ from the string ‘-+abc+-‘:

;SELECT trim('-+abc+-', '-+')
abc
See Also

char(X), charindex(needle, haystack, [start]), endswith(str, suffix), extract(str), group_concat(X, [sep]), group_spooky_hash(str), humanize_file_size(value), instr(haystack, needle), leftstr(str, N), length(str), lower(str), ltrim(str, [chars]), padc(str, len), padl(str, len), padr(str, len), printf(format, X), proper(str), regexp_capture(string, pattern), regexp_match(re, str), regexp_replace(str, re, repl), replace(str, old, replacement), replicate(str, N), reverse(str), rightstr(str, N), rtrim(str, [chars]), sparkline(value, [upper]), sparkline(value, [upper]), spooky_hash(str), startswith(str, prefix), strfilter(source, include), substr(str, start, [size]), unicode(X), upper(str), xpath(xpath, xmldoc)


typeof(X)

Returns a string that indicates the datatype of the expression X: “null”, “integer”, “real”, “text”, or “blob”.

Parameters
  • X* — The expression to check.

Examples

To get the type of the number 1:

;SELECT typeof(1)
integer

To get the type of the string ‘abc’:

;SELECT typeof('abc')
text

unlikely(value)

Short-hand for likelihood(X, 0.0625)

Parameters
  • value* — The boolean value to return


xpath(xpath, xmldoc)

A table-valued function that executes an xpath expression over an XML string and returns the selected values.

Parameters
  • xpath* — The XPATH expression to evaluate over the XML document.

  • xmldoc* — The XML document as a string.

Examples

To select the XML nodes on the path ‘/abc/def’:

;SELECT * FROM xpath('/abc/def', '<abc><def a="b">Hello</def><def>Bye</def></abc>')
        result           node_path  node_attr node_text
<def a="b">Hello</def>  /abc/def[1] {"a":"b"} Hello
<def>Bye</def>          /abc/def[2] {}        Bye

To select all ‘a’ attributes on the path ‘/abc/def’:

;SELECT * FROM xpath('/abc/def/@a', '<abc><def a="b">Hello</def><def>Bye</def></abc>')
result   node_path    node_attr node_text
b      /abc/def[1]/@a {"a":"b"} Hello

To select the text nodes on the path ‘/abc/def’:

;SELECT * FROM xpath('/abc/def/text()', '<abc><def a="b">Hello &#x2605;</def></abc>')
result     node_path    node_attr node_text
Hello  /abc/def/text() {}        Hello 
See Also

char(X), charindex(needle, haystack, [start]), endswith(str, suffix), extract(str), group_concat(X, [sep]), group_spooky_hash(str), humanize_file_size(value), instr(haystack, needle), leftstr(str, N), length(str), lower(str), ltrim(str, [chars]), padc(str, len), padl(str, len), padr(str, len), printf(format, X), proper(str), regexp_capture(string, pattern), regexp_match(re, str), regexp_replace(str, re, repl), replace(str, old, replacement), replicate(str, N), reverse(str), rightstr(str, N), rtrim(str, [chars]), sparkline(value, [upper]), sparkline(value, [upper]), spooky_hash(str), startswith(str, prefix), strfilter(source, include), substr(str, start, [size]), trim(str, [chars]), unicode(X), upper(str)


zeroblob(N)

Returns a BLOB consisting of N bytes of 0x00.

Parameters
  • N* — The size of the BLOB.