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?

Improve this question
5
  • 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
    Commented Jun 28, 2021 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
    Commented Jun 28, 2021 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
    Commented Jun 28, 2021 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
    Commented Jun 29, 2021 at 3:58
  • Written up as an answer now!
    – Spacedman
    Commented Jun 29, 2021 at 8:54

1 Answer 1

Reset to default
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...

Improve this answer
1
  • 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
    Commented Jul 1, 2021 at 13:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.