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.
quote(val)
returns a string that, as expression, evaluates to the same value as was quoted.
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
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;
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
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