Search notes:

SQLite functions

hex(val) returns a hexadecimal representation of the internally stored bytes for val.
random() returns a pseudo-random number in the range between -9223372036854775808 and +9223372036854775807.
randomblob()
val regexp(pat): search for records with regular expressions.
substr(str, beg, len)
quote(val) returns a string that, as expression, evaluates to the same value as was quoted.
sqlite_version() returns a the version of the running SQLite library.
typeof(val) returns a string that indicates the datatype of val.
upper(str) and lower(str) convert ASCII characters to upper or lower.
zeroblob(n) returns a blob with n bytes, each being 0.
Date and time related functions (time, date, datetime, julianday, strftime).

changes

create table tq84_changes(col);

select changes();
-- 0

insert into tq84_changes values (1);
select changes();
-- 1

insert into tq84_changes values (2);
select changes();
-- 1

insert into tq84_changes select * from tq84_changes;
select changes();
-- 2

delete from tq84_changes where col=1;
select changes();
-- 2
Github repository about-sqlite, path: /functions/changes.sql
Compare with total_changes
In SQLite's shell, this function is automatically called if .changes on is set.

replace

create table tq84_texts (
  val text
);

insert into tq84_texts values ('foo, the "bar", and the >baz<');

.mode column
.width 20 20
select val, replace(val, '"', '') from tq84_texts;
--
-- foo, the "bar", and   foo, the bar, and th

select val, replace(val, '>baz<', 'baz') from tq84_texts;
--
-- foo, the "bar", and   foo, the "bar", and 

drop table tq84_texts;
Github repository about-sqlite, path: /functions/replace.sql

total_changes

create table tq84_totalchanges(a, b, c);

insert into tq84_totalchanges values (1, 2, 3);
insert into tq84_totalchanges values (4, 5, 6);
insert into tq84_totalchanges values (7, 8, 9);
delete from tq84_totalchanges where a = 4;

select total_changes();
-- 4

delete from tq84_totalchanges where a = 4;
select total_changes();
-- 4

insert into tq84_totalchanges select a/2, b/2, c/2 from tq84_totalchanges;
select total_changes();
-- 6


drop table tq84_totalchanges;
select total_changes();
-- 6
Github repository about-sqlite, path: /functions/total_changes.sql
Compare with changes
In SQLite's shell, this function is automatically called if .changes on is set.

modulo

with num_generator(n) as (
  select 0 n union all
  select 1+n from num_generator
  limit 15
)
select
  n,
  n % 7 n_mod_7
from
  num_generator;
-- 
-- 0|0
-- 1|1
-- 2|2
-- 3|3
-- 4|4
-- 5|5
-- 6|6
-- 7|0
-- 8|1
-- 9|2
-- 10|3
-- 11|4
-- 12|5
-- 13|6
-- 14|0
Github repository about-sqlite, path: /functions/modulo.sql

See also

SQLite

Index