Fuzzy text searching

How would I go about constructing an endpoint that would return fuzzy searches using the Soundex, Metaphone or Levenshtein algorithms?

As an example, I have a users table with username and full_name and I have an items table with name and description. When I search for say red I want all the items that loosely match the word red, in any of the fields I mentioned to be returned as an array of users and items.!

here’s a screenshot of the react component I want to link to the api

This is the mock data I’ve used, but I’m happy to be more flexible

    const results = [
      {
        type: "person",
        id: "abfazODg",
        name: "Redrigo",
        username: "redrigo64"
      },
      {
        type: "person",
        id: "abfazODg",
        name: "Redrigo",
        username: "redrigo64"
      },
      {
        type: "person",
        id: "abfazODg",
        name: "Redrigo",
        username: "redrigo64"
      },
      { type: "item", id: "abfazODg", result: "red" },
      { type: "item", id: "fbfazODs", result: "redburn" },
      { type: "item", id: "vbfazODe", result: "red flame" },
      { type: "item", id: "xbfazODn", result: "red flame roses" },
      { type: "item", id: "tbfazODw", result: "my red face" }
    ];

Hi @Will here’s a tutorial on accomplishing this that I made for a user a couple weeks ago:

@michael that’s a great video, it helped me a lot.

Is the word includes the same as doing…

SELECT *
FROM users
ORDER BY SIMILARITY(name,'Redrigo') DESC
LIMIT 10;

If I wanted to go a bit further could I then do

SELECT *
FROM users
WHERE SOUNDEX(name) = SOUNDEX('Redrigo');
LIMIT 10;

or

SELECT name,
   METAPHONE(name,10)
FROM users
LIMIT 10;

By making the search more fuzzy I aim to always give the user some results, nothing worse than typing something and getting northing back at all.

In my example, includes is saying is this input value included anywhere in the fields I’m filtering on.

I think if you want to show results per letter type it then it would likely be an API call per letter entered in.

Thank you @michael, I agree it would be an API call per the letter entered. Does Xano offer any other forms of searching other than includes at this time? Is there any plans to offer a more fuzzy search?

Yes, all the available filters and operators should be able to be used to perform certain search/filter parameters besides includes… That might be something we explore adding in the future!

1 Like