Repository navigation
Range operator for Filter #935
Description
If I have an INTEGER column I can filter the numbers in it using the supported operators from this page https://github.com/sqlitebrowser/sqlitebrowser/wiki/Using-the-Filters.
Is it possible to have a filter for range (- or ~)? Something like min-max (or min~max) to show only the numbers in this range. For example, If I want to filter a column with numbers between 1 and 5 I can type in filter something like 1-5 (or 1~5). It is like combining Equal to or greater and Equal to or less together.
Also, maybe we can have an and (,) operator? To show only specific numbers we can type something like 1,2,5.
I'm opening this issue because:
- DB4S needs a feature
Activity
justinclift commentedon Jan 17, 2017
Sounds like a worthwhile enhancement. We could probably do something to make this work. 😄
@MKleusberg Thoughts?
MKleusberg commentedon Jan 27, 2017
We've just merged a PR by @schdub that implements this (using the
1~5notation) 😃@karim Can you test tomorrow's nightly build to see if it's working for you? 😃
karim commentedon Jan 28, 2017
@MKleusberg Thanks. Yes I will try it and report back.
karim commentedon Jan 28, 2017
@MKleusberg I tested it and it is working very nice. Thanks!
@schdub Thanks a lot for this! 😄
justinclift commentedon Jan 30, 2017
@chrisjlocke Any interest in updating the Filters page on the wiki with this new operator? 😄
chrisjlocke commentedon Jan 30, 2017
Just updating the wiki, but ... well, its not working for me....
1 ~ 6 shouldn't be including 11 should it? If I change it to 2~6, I get the right result.
I'll ... er... doctor the screenshot until its resolved! ;)
chrisjlocke commentedon Jan 30, 2017
Wiki page updated. 👍
References to 'search' also changed to 'filter' as it was slightly cross-purpose and ... well, it made me twitch too much.... ;)
MKleusberg commentedon Jan 30, 2017
@chrisjlocke I've stumbled upon that, too 😉 But that's actually because of SQLite's treatment of data types.
Field3is probably of typetextin your database. If you change it tointegerSQLite it'll work as expected. That's just how SQLite works: you can save any kind of data in any column, no matter the column's data format. But as soon as you're querying the table the data format of the columns actually does matter. In this case 11 is between 1 and 6 because 11 is a text, not a number, and only the first character is used for comparison. If the data format of the column is integer, the value is basically casted to an integer before comparison and 11 is definitely not between 1 and 6 😉Going one step further we could think about casting the field to an integer automatically when doing range comparisons but I'd suggest to leave it as it is in order to not hide the logic of SQLite. But we might add an 'attention' box to the Wiki page. What do you reckon?
chrisjlocke commentedon Jan 30, 2017
Good spot - yup, the field type was text.
*blush mode on*. Doh! ;)Changing it to integer works as required. (
*flushes head down toilet*)MKleusberg commentedon Jan 30, 2017
No worries, it took me some time to figure this out, too 😃 And it wasn't even the first time this confused me 😉
karim commentedon Jan 31, 2017
Maybe DB4S should provide a hint when the
~(range) operator is being used on a column that is not anINTEGER. A tooltip, modeless dialog, or changing the background color of the column's filter?This should work also with
>(greater than) and<(less than) operators, since they are only used with numbers.karim commentedon Aug 13, 2018
Sorry, I forgot to close this. 😃
pablonana commentedon Feb 3, 2021
this doesn't work for float fields, can you fix that?
chrisjlocke commentedon Feb 3, 2021
Can you provide an example of 'doesn't work'? Seems to work OK for me...
This is for values 1.1, 2.2 and 3.3
chrisjlocke commentedon Feb 3, 2021
Even works with non-integers...
MatthieuGomes commentedon Feb 28, 2025
Sorry to reopen : doesn't work for datetime for example