Leandro Silva leandrosilva
leandrosilva
/ fts.sql
Created
October 6, 2022 20:47
Full Text Search "sort of" with prefix, infix, fuzzy, and similarity on the Almighty Postgres
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT | |
| pos, | |
| sim, | |
| p.* | |
| FROM | |
| produtos AS p, | |
| NULLIF(POSITION('seme' IN LOWER(COALESCE(p.gtin, '') || ' ' || COALESCE(p.descricao, ''))), 0) AS pos, | |
| NULLIF(SIMILARITY(LOWER(COALESCE(p.gtin, '') || ' ' || COALESCE(p.descricao, '')), 'seme'), 0) AS sim | |
| WHERE | |
| (LOWER(COALESCE(p.gtin, '') || ' ' || COALESCE(p.descricao, ''))) LIKE 'seme%' |
OlderNewer