Feb 10, 2017

Sqlite: how to generate a random number within range 0..99

Random()

The Sqlite random() function will returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807.

abs() will returns the absolute value of the numeric argument

floor() or round() will return 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.

NB:floor() will only work within SQLExplorer, Dbeaver, ...

Result

select floor(abs(random()/92233720368547758.07));
will generate within 0..99

and
select round(abs(random()/92233720368547758.07),0);
will generate within 1..100

Sqlite: how to count words in a field

Assuming a table like this:

create table test (id integer primary key autoincrement, name text);

and values like:

insert into test values (null, "test") ;
insert into test values (null, "word1 word2 word3")  ;
insert into test values (null, "word4 word5 word6")  ;
insert into test values (null, "word7 word8 word9 word10 word11 word11")  ;

here is the result:

select id, name from test

1;test
2;word1 word2 word3
3;word4 word5 word6
4;word7 word8 word9 word10 word11 word12

Counting

Now you want to count the word(s) in the field "name" - here is how to proceed:
select (length(name)-length(replace(name," ","")))+1 as wordcount, name from test

it works because the query is looking for the number of space - a space means a new word is in the field and that is why you need to add 1 to the total.

Sqlite is awesome

Sep 26, 2016

Sqlite - how to search a table with a list of terms from another table

Here is a little example of how brilliant Sqlite is :

sqlite> .schema  country
CREATE TABLE country(id integer primary key autoincrement, name text);
sqlite> .schema search
CREATE TABLE search(term text);
sqlite> select * from country ;
id|name
1|France
2|Czech Republic
3|Italy
4|Great Britain
5|Ireland
6|Slovakia
7|Senegal
sqlite> select * from search ;
term
%fr%
%cz%
%ir%
ai
sqlite> select a.name from country a join search b on a.name like "%"||b.term||"%" ;
name
France
Czech Republic
Great Britain
Ireland

NB : the || is used for Sqlite for concatenation 

LinkWithin

Related Posts Plugin for WordPress, Blogger...