An sqlite3 database browsing problem ...

Giganews Newsgroups
Subject: An sqlite3 database browsing problem ...
Posted by:  R.Wieser (addre…@not.available)
Date: Mon, 15 Feb 2021

Hello all,

I'm trying to browse a random sqlite3 database and would like to sort on an
also random collumn.  I've got two problems:

1) the text collumns contents may be longer than I care to handle.  Meaning
that I want to do a string compare only upto a certain number of characters.

2) the selected "select on" column may be numeric.

Some googeling gave me SUBSTRING.  Alas, that doesn't go well if the target
column is numeric  (lots of entries get skipped).

Some more googeling gave me LIKE. But for some reason I think that adding a
"%" to the end of a value (in string form) will also turn the value into an
actual string, causing the same problem.

So, the question: How do I do a WHERE and SORT BY which will work with
limited length provided strings as well as values.  Both retrieved as
strings and provided parameterized.

Example of a query :

SELECT * FROM %s
WHERE (SUBSTR(%s,1,63), %s) >= (?1, ?9)
ORDER BY %s , %s LIMIT %d;

And yes, that is a "sprintf" string :-)

"?1" is the "sort on" collumn, which may be string or nmeric, and "?9" the
first, key collumn - as a fallback if the "sort on" column contains (more
than) a screenfull of the same data.

Regards,
Rudy Wieser

Replies