1

I'm trying to use Northwind version in SQLite (someone posted it on Github and it's super handy), but my query for selecting employees with last name starting with B, C, D, ..., L using LIKE returns empty table:

SELECT Title
FROM Employees
WHERE LastName LIKE '[B-L]%'

The table contains such names (most of them, in fact). Does SQLite not support character range in LIKE with []?

qalis
  • 1,314
  • 1
  • 16
  • 44
  • 1
    The SQL standard does not use LIKE for regular expressions. The only wildcards for LIKE are `_` and `%` –  Oct 22 '20 at 06:50

2 Answers2

2

SQLite does not support this SQL Server - like functionallity that you want.
You can do it with SUBSTR():

WHERE SUBSTR(LastName, 1, 1) BETWEEN 'B' AND 'L'

or:

WHERE LastName >= 'B' AND LastName < 'M'
forpas
  • 160,666
  • 10
  • 38
  • 76
  • upvoted, I am a fair player and this is a good solution ;) It would be interresting to test the efficiency diffrence between the REGEXP and this over large datasets – Thomas G Oct 21 '20 at 19:34
  • 1
    I forgot about the `SUBSTR`, nice and clean solution, thank you. – qalis Oct 21 '20 at 19:48
1

A solution is to create a user defined function that allows you to use regular expressions from a Python module, see explanations here https://stackoverflow.com/a/8338515/4197505

Then you can do :

WHERE LastName REGEXP '^[B-L]'
Thomas G
  • 9,886
  • 7
  • 28
  • 41
  • 1
    Unfortunately, according to the docs (https://www.sqlite.org/lang_expr.html#regexp) "No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message". Also I'm using JDBC Xerial driver, which also (https://github.com/xerial/sqlite-jdbc/issues/60) does not implement REGEXP. – qalis Oct 21 '20 at 19:22
  • @qalis, my bad it is a custom function. I edited my answer – Thomas G Oct 21 '20 at 19:28
  • So it seems there's no built-in functionality then - alright, I can live with that, thanks anyway for pointing me in the right direction. – qalis Oct 21 '20 at 19:31
  • It is now builtin to the sqlite3 shell – user1461607 Dec 26 '21 at 12:46