how to write a query by using some filter patterns in SQL Server?

Question

what I want is to apply some filter patterns to search the data. For example. There is a large physical table in the database regarding medicines, I want to allow search even if user write lasix, lasixx, lasixxx, lasax, lasi, lisax. Data should be retrieved for the medicine lasix. How can I write these filter patterns in SQL query? And this query should be compilent with old SQL server versions like 2008 as well. I want to do it in SQL Server side instead of c# logic. Help and assistance is really appreciated.

Answer 1

Sqlserver supports a soundex column.

https://learn.microsoft.com/en-us/sql/t-sql/functions/soundex-transact-sql?view=sql-server-ver16

the other common approach is an alias table.

https://www.red-gate.com/simple-talk/databases/sql-server/t-sql-programming-sql-server/fuzzy-searches-sql-server/