In mysql, the ____ is used as a wildcard to represent any collection of characters.

In this article, we are going to learn how to use the SQL LIKE operator, in SQL Server, using regular expressions to find and/or manipulate text. We will start by learning the symbols and basic syntax of using wildcard regular expressions. We will use character sets and repetition expressions to create flexible matching patterns, and along the way, we’ll examine different ways to use the LIKE operator. And then, finally, in the latter part of the section, we will explore some of the most common and most useful regular expression examples.

SQL is the most commonly used language to work with databases. When you design a report or use BI or any reporting tool, the software is almost certainly building an SQL query behind the scenes which runs on the database and returns your selected data. When we’re looking for specific data or the data that fits specific criteria, the where clause provides the toolset you need. This gives an option to query specific rows that we’re looking for instead of the entire table.

Pre-requisites

Download the AdventureWorks2014 database here to test the following T-SQL samples.

Getting Started

Let us walk-through the SQL statements using the LIKE keyword and wildcard characters. So, let’s get started learning about SQL LIKE operator.

Using SQL LIKE Wildcard Character examples

Regular expressions are patterns for describing how to match strings in a WHERE clause. Many programming languages support regular expressions that use slightly different syntax from what is used with the LIKE operator. In this article, when we refer to regular expressions, we’re referring to the patterns used with the SQL LIKE operator

The following table includes the four different wildcard characters. You can also refer the article SQL string functions for Data Munging (Wrangling) for more examples.

Using SQL LIKE with ‘%’ wildcard character

The following SQL statement returns all of the rows of person table where their last name starts with the letter A. Let us specify the letter ‘A’, the first character that needs to be in the string and then use the wildcard ‘%’, the percent.

You’ll see the output that lists top 10 rows of the person table where the firstname starts with A and the rest of the character is unknown.

Using SQL LIKE with the ‘_’ wildcard character

The wildcard, underscore, is for matching any single character. The following SQL statement finds all telephone numbers that have an area code starting with 7 and ending in 8 in the phonenumber column. We’ve also included % wildcard character at the end of the search pattern as we’re not concerned with the rest of the string values.

The output shows that the area code of that start with 7 and ends with 8 are listed.

Using SQL LIKE with the [ ] wildcard characters

Square brackets e.g [ ] allow us to identify multiple single characters that would be in that particular position. For example, let’s say to list all the rows where first names third character start with I or K. Instead of writing multiple LIKE conditions, we can place the pattern matching set in the third position and close it in the square. The query engine first looks for ‘I’ and then looks for ‘K’.

Let’s execute the following SQL statement

The above query can be re-written using OR condition. It’s more like an OR condition.

In the output, we can see that last names where the third character is ‘I’ or ‘k’ are listed

Using SQL LIKE with the ‘^’ wildcard character

The following SQL statement displays all the rows that do not have the letter that starts with A to D in the first character of their last name. In order to that place the tilde character in the first position of the pattern. It becomes a NOT condition.

Now, if I run the above query, we’ll see that all the names coming back do not have an A, B, C or D as their first character.

Using SQL NOT LIKE with the [] wildcard characters

The following SQL statement finds all the persons where the first name column has more than 3 characters.

The output list only those names where the length of the firstname is more than 3

Using SQL LIKE with the ESCAPE clause

In the following SQL statement, the ESCAPE clause is used to escape the character ‘!’ to negate the meaning of ‘%’ to find the string ‘100% Free’ in the column col1 of the temp table.

The output list only those values where the search pattern ‘100% Free’ matches the col1 expression.

Using SQL LIKE with the CASE statement

The following SQL statement pulls out all of the employees that have a phone number formatted like three-three-four digits with dashes in between (999-999-9999). The pattern is then compared with phonenumber column to derive the domestic or international categories.

The case expression is evaluated for the specific pattern to derive the phone category type.

In the output, we can see the number is classified as domestic or international. The phonenumber column is evaluated with the LIKE operator using the square bracket. The number zero to nine in the first character position is evaluated for matching pattern zero to nine, any number from zero to nine in the second character position and third and then the fourth character position must be a dash and similar logic is applied to the rest of the characters.

Using SQL LIKE with dynamic SQL

The following SQL statement returns all the employees where the lastname matches the pattern Barb. The pattern is dynamically created and compared against the expression.

The output list the matching rows for the specified pattern Barb

Note: By default, CHAR injects trailing blanks depending on the length of the field. Use RTRIM to suppress the trailing blanks, if you’re using the char data-type.

In the following SQL statement, the @eLastName field is of char data type. You can see a use of RTRIM function to trim the trailing blanks.

Using SQL Like with an IF statement

The following SQL statement, the input value is evaluated for the specific pattern in the condition clause using IF statement.

The input string is evaluated for specific patterns using SQL like wildcard expression and returns valid input string.

That’s all for now!

Summary

Thus far, we discussed various tips and four different wildcards (%,_,[], and ^] that are available with the SQL LIKE operator. It is a great searching technique for matching string of characters with the specified patterns or where we’ve not quite sure of what you’re searching aka fuzzy search. The available wildcard characters make the LIKE operator more flexible. I hope you enjoyed this article on the SQL LIKE operator in SQL Server. Feel free ask any questions in the comments below.

• Author
• Recent Posts

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram

What is the wildcard character in MySQL?

Is * a wildcard in SQL?

What is like %% in SQL?

Which of the following is the wildcard used for any number of characters in SQL?