SQL query : finding a word in a shapefile

Question

I have a punctual shapefile containing the name of geographic locations (attribute 'name')

Using DB Manager of Qgis, I would like create an SQL query to find, within the shape, the name of a location and, and therefore its point. I would like to find the name of the location even by writing only one part.

For example: The name of location is 'Torretta'. Just writing "Torr", I would like to find all the locations that contain the word "Torr" (without considering the upper case and lower case letters).

Answer 1

One example working with QGIS Alaska dataset:

SELECT * 
FROM "airports"
WHERE lower("NAME")  LIKE lower('%Big%') 

• FROM: your shapefile layer shown as a Virtual Layer
• "NAME" is "name" in your case
• '%Big%' would be '%Torr%'

Hitting [Load now!] button (at lower right corner), selected Big Lake and Big Mountain AFS appear as a new QueryLayer.

EDIT To narrow down the selection by using both "name" and "town", please add AND keyword.

SELECT * 
FROM "your_shapefile_layer"
WHERE lower("name") LIKE lower('%Torr%') AND lower("town") LIKE lower('%potenza%')