Is there a way to search for a matching string with multiple variants in a column?

I have a table that contains the colum “CompanyName”. I want to get all rows that mach with a specific string.

+----+----------------------+-------------------+---------+---------+
| Id | CompanyName          | Street            | City    | Country |
+----+----------------------+-------------------+---------+---------+
| 1  | Microsoft Corporation| One Microsoft Way | Redmond | USA     |
+----+----------------------+-------------------+---------+---------+

Is there a way to search for “Microsoft Corporation” or “Corporation Microsoft” and get back row with id ‘1’?

My problem is, if my customer is searching for “Corporation Microsoft” in my WebApplication he will not get row 1 return with the following code for lookup:

SELECT * FROM Customers WHERE CompanyName LIKE %searchPattern%

The WebApp is coded in C#.

SQL-Version: SQL Server 2017 (14.0)

Thank you in advance

Answer

You need full-text installed in your instance, then you can do :

SELECT * FROM COMPANY WHERE CONTAINS(CompanyName,'Corporation Microsoft')

Follow this guide to setup full-text : https://www.sqlshack.com/sql-filestream-and-sql-server-full-text-search/

Read more about full text search : https://docs.microsoft.com/fr-fr/sql/relational-databases/search/full-text-search?view=sql-server-2017

And CONTAINS function : https://docs.microsoft.com/fr-fr/sql/t-sql/queries/contains-transact-sql?view=sql-server-2017