Are you new to T-SQL programming and want to learn about the SQL LIKE statement? Do you wonder how it differs from the = (equals) syntax in a WHERE clause? Are you curious about including wildcard characters to match patterns and how it affects the performance of SQL queries? In this article, we will explore the SQL LIKE operator and its wildcard options.

Overview – SQL LIKE Operator

The SQL LIKE operator is used to compare string values (VARCHAR/NVARCHAR data types in SQL Server) to one another. It works similarly to the = (equal sign) operator, but with the added capability of using wildcards to match patterns within a string. The LIKE operator can be used in various SQL statements, such as IF statements, WHERE clauses, and HAVING clauses.

Consider the following query:

SELECT * FROM Sales.Invoices
WHERE ConfirmedReceivedBy LIKE N'Ingrida Zeltina';

In this query, the LIKE operator is used in the WHERE clause to compare the value of the column “ConfirmedReceivedBy” with the string ‘Ingrida Zeltina’. The LIKE operator, in this case, functions the same as the = operator.

Using Wildcard Characters to Match Patterns

The advantage of using the LIKE operator over the = operator is its support for pattern matching with wildcard characters. Let’s explore some of these wildcard options:

% (Percent Sign) Wildcard

The % wildcard is used to represent a pattern of any number of characters, including zero characters. For example:

DECLARE @String NVARCHAR(4000);

SELECT @String = DeliveryInstructions FROM Sales.Invoices WHERE InvoiceID = 63452;

IF @String LIKE N'%Escobar Crescent'
BEGIN
  PRINT 'It is on Escobar Crescent';
END;

In this script, the % wildcard is used to check if the value of @String ends with the words “Escobar Crescent”. It can match strings like “Escobar Crescent”, “123 Escobar Crescent”, or “Drop off items at Escobar Crescent”.

_ (Underscore or Underbar) Wildcard

The _ wildcard represents exactly one character of any kind. It cannot represent a string of zero characters. For example:

SELECT DISTINCT DeliveryInstructions 
FROM Sales.Invoices 
WHERE DeliveryInstructions LIKE 'Suite 2_, 1300 Sonkar Road';

In this query, the _ wildcard is used to find all delivery instructions delivered to a suite starting with a 2 on 1300 Sonkar Road. It would not include Suite 2 as there needs to be one more character after the 2.

[…] (Brackets) Wildcard

The bracket pair wildcard works similarly to the _ wildcard, representing exactly one character. However, it defines a universe of possible values for the one character within the brackets. For example:

SELECT *
FROM [WideWorldImporters].[Sales].[SpecialDeals]
WHERE DealDescription LIKE '%10[%]%';

In this query, the bracket pair wildcard is used to find any sales that had a 10% discount applied. The % character is treated literally and not as a wildcard value.

Inversing a […] Wildcard with the NOT Option

By placing a ^ (carat) in front of a bracket pair wildcard, the pattern match algorithm will look for exactly one character that is anything but a character listed in the universe within the brackets. For example:

SELECT CustomerName, SC.PostalPostalCode, CN.CountryName
FROM Sales.Customers SC
  INNER JOIN Application.Cities AC ON SC.DeliveryCityID = AC.CityID
  INNER JOIN Application.StateProvinces AP ON AC.StateProvinceID = AP.StateProvinceID
  INNER JOIN Application.Countries CN ON AP.CountryID = CN.CountryID
WHERE CountryName = 'United States'
  AND SC.PostalPostalCode LIKE '%^[0-9]%';

In this query, the ^ wildcard is used to find any postal or zip codes for U.S. addresses that contain non-numeric characters.

Final Thoughts

When using arguments with wildcards in a WHERE or HAVING clause, it’s important to note that they are rarely searchable. This means they can cause a table scan and lead to slow queries, especially on larger tables. To mitigate this, it’s recommended to include other searchable arguments in the same clause to reduce the row count and only perform the pattern match on a smaller subset of rows.

Understanding the SQL LIKE operator and its wildcard options can greatly enhance your ability to search and match patterns within string values in SQL Server. By leveraging these powerful tools, you can efficiently query and manipulate data to meet your specific needs.

Thank you for reading this article. We hope you found it informative and helpful in your SQL Server journey.

Article Last Updated: 2022-10-27