3 different ways to do partial text matching on Rockset

:question:QUESTION:
How to do partial text matching on Rockset

:white_check_mark:ANSWER:
Let’s say you want to do prefix matching like query=Fer would match Ferro. You can do 3 things:

1| use LIKE
for example: name LIKE 'someNamePrefix%'

2| Use ARRAY_CONTAINS():
First, you can manually split the word you want to match:

[“Ferro”,
“Ferr”,
“Fer”,
“Fe”,
“F”]

Then you can use ARRAY_CONTAINS(_array, ‘Fer’)

3| Use the SEARCH() function
The SEARCH() has the HAS_TERM() feature:

WHERE
    SEARCH(
        HAS_TERM(words, 'Go'),
        HAS_TERM(words, 'Ferro')
    )

so if words contain Go and Ferro it’ll return.

You can also use the SEARCH() with the BOOST() feature. The BOOST() boosts the value of a term when it occurs. In the example below, if words contain Ferro it would get 3 points:

WHERE
    SEARCH(
        HAS_TERM(words, 'Go'),
        BOOST(3, HAS_TERM(words, 'Ferro'))
    ) OPTION(match_all = false)