How Does SQL Server Search Work?
The LIKE operator is used in a WHERE clause in SQL to search for a specific pattern in a column.

The wildcards % and _ are frequently used in the LIKE operator.
% denotes one or more characters, as in Acc% — Account, Accept, Access. _ denotes a single character, as in Labs, Cabs, and Tabs are all abbreviated as _abs.

What exactly is Normal Search?
The search is limited to a single column. Filter the results depending on the single column's search text.
The column Department Name is searched in the sample below.

DECLARE @SearchText VARCHAR(255)

SET @SearchText = 'engineer'

SELECT
    EmployeeKey,
    FirstName,
    LastName,
    Title,
    EmailAddress,
    Phone,
    EmergencyContactName,
    DepartmentName
FROM dbo.DimEmployee WITH(NOLOCK)
WHERE DepartmentName LIKE '%' + @SearchText + '%'

In the below example, search is applied to the column FirstName.
DECLARE @SearchText VARCHAR(255)

SET @SearchText = 'david'

SELECT
    EmployeeKey,
    FirstName,
    LastName,
    MiddleName,
    Title,
    HireDate,
    BirthDate,
    EmailAddress,
    Phone,
    EmergencyContactName,
    EmergencyContactPhone,
    DepartmentName,
    StartDate
FROM dbo.DimEmployee
WHERE FirstName LIKE '%' + @SearchText + '%'

What is an Advanced Search?
Multiple columns are searched. Filter the data depending on the search text in the numerous columns.
Search is used on numerous columns in the example below.

DECLARE @SearchText VARCHAR(255)

SET @SearchText = 'tool'

SELECT EmployeeKey, FirstName, LastName, MiddleName, Title, HireDate, BirthDate, EmailAddress,
Phone, EmergencyContactName, EmergencyContactPhone, DepartmentName, StartDate
FROM dbo.DimEmployee
WHERE (FirstName LIKE '%' + @SearchText + '%'
  OR LastName LIKE '%' + @SearchText + '%'
  OR Title LIKE '%' + @SearchText + '%'
  OR EmailAddress LIKE '%' + @SearchText + '%'
  OR EmergencyContactName LIKE '%' + @SearchText + '%'
  OR DepartmentName LIKE '%' + @SearchText + '%'
  OR HireDate LIKE '%' + @SearchText + '%'
  OR COALESCE(@SearchText,'') = '')

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.