Search for a String in a Single Table in SQL Server

In SQL Server, you can search for a string in a single table using the `LIKE` operator or the `CONTAINS` function. This is useful when you want to find specific data that matches a certain pattern or phrase.

Using the LIKE Operator

The `LIKE` operator is used to search for a specified pattern in a column. The basic syntax is:

SELECT column_name(s) FROM table_name WHERE column_name LIKE '%pattern%';

Where:

  • `column_name(s)` is the column(s) you want to search.
  • `table_name` is the table that contains the data.
  • `column_name` is the column you want to search.
  • `pattern` is the string you want to search for.
  • `%` is a wildcard character that matches any characters before or after the pattern.

Example 1: Search for a String using LIKE

Let's say we have a table called `Employees` with a column called `EmployeeName`, and we want to find all employees whose name contains the string "John":

SELECT EmployeeName, Department FROM Employees WHERE EmployeeName LIKE '%John%';

The result set would return all employees whose name contains the string "John", such as "John Smith", "John Doe", etc.

Using the CONTAINS Function

The `CONTAINS` function is used to search for a specified phrase or word in a column. The basic syntax is:

SELECT column_name(s) FROM table_name WHERE CONTAINS(column_name, 'phrase');

Where:

  • `column_name(s)` is the column(s) you want to search.
  • `table_name` is the table that contains the data.
  • `column_name` is the column you want to search.
  • `phrase` is the string you want to search for.

Example 2: Search for a String using CONTAINS

Let's say we have a table called `Products` with a column called `ProductDescription`, and we want to find all products whose description contains the phrase "high quality":

SELECT ProductName, ProductDescription FROM Products WHERE CONTAINS(ProductDescription, 'high quality');

The result set would return all products whose description contains the phrase "high quality".

Important Notes

When searching for strings in SQL Server, keep in mind:

  • The `LIKE` operator is case-sensitive, so "John" and "john" would be treated as different strings.
  • The `CONTAINS` function is case-insensitive, so "high quality" and "High Quality" would be treated as the same phrase.
  • Use the `LIKE` operator for simple pattern matching, and the `CONTAINS` function for more complex phrase searching.