Hello everyone! I'm facing a challenge while attempting to filter through names in a JSON using SQL. I'd appreciate any insights or suggestions on this.
Goal:
I have a dataset where names and surnames might be present in a single column. For example:
- Leticia Aguilar Rodriguez
- Leticia Garza Tames
- Leticia Alondra Solano Martinez
When I type "Leticia" followed by a space in a search bar, I want the system to continue showing all names starting with "Leticia", irrespective of whether they have a second name. This allows me to further filter by surnames or any subsequent names.
Issue:
Currently, upon hitting space after "Leticia", entries like "Leticia Garza Tames" disappear from the results. I have tried various SQL query structures to address this, but without success.
Query Tried:
I've been working with the following structure:
SELECT *
FROM {{ table1.data }}
WHERE
lower(nombre) LIKE lower({{textInput8.value}}) || '%' OR
lower(apellidos) LIKE substr(lower({{textInput8.value}}), instr(lower({{textInput8.value}}), ' ') + 1) || '%'
(Note: nombre refers to the name column and apellidos refers to the last name.)
Any help would be greatly appreciated. Thanks in advance!
