Search Functionality in SQL Server

How Does Search Work in SQL Server?

In SQL, to search for a specified pattern in a column, the LIKE operator is used in a WHERE clause.

The % and _ are two often user wildcards in the LIKE operator.

• % represents one or more characters, example: Acc% — Account, Accept, Access.
• _ represents a single character, for example: _abs — Labs, Cabs, Tabs.

What is Normal Search?

Search is applied to the single column. Filter out the records based on the search text on the single column.

In the below example, search is applied to the column Department Name.

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?

Search is applied on multiple columns. Filter out the records based on the search text on the multiple columns.

In the below example, search is applied on multiple columns.

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,'') = '')