2

I know that on PostgreSQL I can filter columns using a regular expression as the following:

select * from mytable WHERE mycolumn ~ '^word.*'

It works perfectly fine on PostregreSQL. However if I try this same syntax on SpatiaLite I see the following message: SQL error: near "~": syntax error. Then, I thought SpatiaLite would use the same syntax as SQLite, so I found this solution on StackOverflow that's a database-related question. However, using select * from mytable WHERE mycolumn REGEXP '^word.*' hasn't solved the problem as well... So I think it's more a syntax issue with SpatiaLite than an SQL problem here. I've searched words like regex and regular expression on the SpatiaLite documentation but it doesn't mention this subject there. Is it a limitation from SpatiaLite? Can I search values inside a SpatiaLite database using a regular expression?

raylight
  • 1,241
  • 4
  • 19
  • When you say "using select * from mytable WHERE mycolumn REGEXP '^word.*' hasn't solved the problem " do you get the same error or can you not get that to work in sqlite3 because you've not got the required supplementary regex module loaded into your sqlite3? Do the examples in that Q not work? What does select 1 where 'a' REGEXP 'b'; say? That's about the minimal regex test you can do in sqlite3 (should return nothing, replace 'b' with 'a' and it should return "1"). – Spacedman Jun 28 '21 at 19:58
  • @Spacedman It works on sqlite3 after loading the module as described here, I'm using Ubuntu as well... Now that you said it, I've tried opening this database on the command line with SpatiaLite and if I load the module, the REGEXP works fine. It partially solves the problem because now I can extract the information that I want with ogr2ogr, but on the other hand I don't know if it's possible to load this module on spatialite-gui (I've tried using .load /usr/lib/sqlite3/pcre.so inside its gui interface but it didn't work. – raylight Jun 28 '21 at 20:11
  • In the spatialite-gui, load the extension from the SQL function rather than the command-line dot-function - run this from an SQL window when you've opened your database: select load_extension('/usr/lib/sqlite3/pcre.so'); – Spacedman Jun 28 '21 at 20:46
  • @Spacedman Cool, the issue is solved for me... The only observation is that REGEXP returns an error when mycolumn has null values so I need to use is not NULL with it as well (I spent some time figuring it out here). – raylight Jun 29 '21 at 03:58
  • Written up as an answer now! – Spacedman Jun 29 '21 at 08:54

1 Answers1

3

The linked answer (well found!) says to load the regex module using the .load command on the sqlite3 command line. This also works on the spatialite command line:

spatialite> select 1 where 'a' regexp 'b';
Error: no such function: regexp
spatialite> .load /usr/lib/sqlite3/pcre.so 
spatialite> select 1 where 'a' regexp 'b';
spatialite> select 1 where 'a' regexp 'a';
1

But doesn't work with spatialite-gui because the "dot" command are not SQL and so aren't interpreted. Thankfully there's an SQLite function that does the same thing that you can use in the GUI - load_extension:

enter image description here

This also works in the sqlite3 and spatialite command lines, so you can use it everywhere:

spatialite> select 1 where 'a' regexp 'b';
Error: no such function: regexp
spatialite> select load_extension('/usr/lib/sqlite3/pcre.so');

spatialite> select 1 where 'a' regexp 'b';
spatialite>

That should work in anything that lets you run SQL against SQLite databases - including Spatialite and Geopackage connections...

Spacedman
  • 63,755
  • 5
  • 81
  • 115
  • One edge case that I haven't figured out how to make it work is when I'm using REGEXP with ogr2ogr. If I use select load_extension('/usr/lib/sqlite3/pcre.so') I get permission denied, The way of making it work with spatialite from the command line is by using spatialite "file.sqlite" -cmd ".load /usr/lib/sqlite3/pcre.so" "select * from my_table WHERE my_column REGEXP '^Word.*'".. However, with ogr2ogr I can't use the cmd to define this module. I'm wondering if there's a way of setting it by default just so I don't need to load the extension all the times that I open the database. – raylight Jul 01 '21 at 13:54