Skip to main content
Geographic Information Systems

Return to Question

deleted 5 characters in body
Source Link
raylight
raylight
  • 1.2k
  • 8
  • 25

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 only 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?

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 only 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?

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?

Source Link
raylight
raylight
  • 1.2k
  • 8
  • 25

How can I filter values using a regular expression on SpatiaLite?

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 only 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?