How to search for similar words in SQL Server

Question

I am using CONTAINS and FREETEXT on SQL query to search for text in big text fields.

What I noticed that the search returns result when the exact word match, but what if I want to search for similar words?

For example, when I type Carlo, it did not display anything if what I have is Carlos (with an S)

Below is a simple query similar to the one I use:

SELECT P.*                      
FROM MyTable AS P
WHERE(CONTAINS(P.*, 'Carlo') OR freetext(P.*, 'Carlo'))

How can I make the search bring similar words to Carlo such as Carlos, Carla, etc... without affecting the performance?

Answer 1

Try this

SELECT P.*                      
FROM MyTable AS P
WHERE CONTAINS(P.*, 'FORMSOF(INFLECTIONAL, "Carlo")')

For reference you can check documentation