Microsoft Visual C#: LIKE a Pattern

To create a LIKE operator, if you are visually creating the statement, in the Table window, in the Criteria section, under the Filter column, click the box that corresponds to the column on which the condition would be applied and type.

In a SQL statement, the LIKE operator is used in a formula as follows:

Expression LIKE pattern

The Expression factor is the expression that will be evaluated. This must be a clear and valid expression.

The pattern factor can be a value to be found in Expression. For example, it can be the same type of value used in a WHERE statement. In this case, the equal operator would be the same as LIKE. For example

SELECT DateOfBirth, LastName, FirstName, 
       Gender, State, ParentsNames
FROM Students
WHERE State = N'VA';
GO

is equivalent to

SELECT DateOfBirth, LastName, FirstName, 
       Gender, State, ParentsNames
FROM Students
WHERE State LIKE N'VA';
GO

If you are working in a Windows application, you can assign the LIKE expression to the Filter property of the binding source.

The idea of using a LIKE operator is to give an approximation of the type of result you want.

SELECT FirstName, LastName,
       Gender, State, ParentsNames
FROM   Registration.Students
WHERE NOT State LIKE N'VA';
GO

SELECT FirstName, LastName, 
       Gender, [State], ParentsNames
FROM   Registration.Students
WHERE NOT (State LIKE N'VA');
GO

SELECT FirstName, LastName, 
       Gender, [State], ParentsNames
FROM   Registration.Students
WHERE  NOT State NOT LIKE N'VA';
GO

SELECT FirstName, LastName, 
       Gender, [State], ParentsNames
FROM   Registration.Students
WHERE  NOT (State NOT LIKE N'VA');
GO

If you want to match any character, in any combination, for any length, use the % wildcard. If you precede it with a letter, as in S%, the condition would consist of finding any string that starts with S. Imagine that you want to get a list of students whose last names start with S. You would type the condition as LIKE 'S%'. To do this visually, in the Criteria section, under the Filter column, type the condition. Here is an example:

The SQL statement is this query is:

SELECT SELECT FirstName, LastName, Gender, City, State
FROM   Students
WHERE  (LastName LIKE N'S%')

This would produce:

You can negate this condition by preceding it with NOT. Here is an example:

SELECT SELECT FirstName, LastName, Sex, City, State
FROM   Students
WHERE  (NOT (LastName LIKE N'S%'))

This would produce:

This time, the result is the list of students whose last names don't start with S.

When you precede the % character with a letter, only that letter would be considered. Alternatively, you can specify a group of characters that would precede the % symbol. For example, if you have some first names that start with Ch in a list but you do not remember the end of the name you are looking for, to create the list, you can specify that the first name would start with Ch and end with whatever. In this case, you would use Ch% as follows:

SELECT SELECT FirstName, LastName, Sex, City, State
FROM   Students
WHERE  (LastName LIKE N'Ch%')

This would produce:

Instead of ending a letter or a group of letters with %, you can begin the LIKE statement with %. An example would be LIKE N'%son'. In this case, all strings that end with son, such as Johnson or Colson, would be considered.

If you remember neither the beginning nor the end of a string you want to search for, but you know a sub-string that is probably included in the type of string you are looking for, you can precede it with % and end it with %. An example would be LIKE N'%an%'. In this case, all strings that include "an" anywhere inside would be considered. Here is an example:

SELECT SELECT FirstName, LastName, Gender, City, State
FROM   Students
WHERE  (LastName LIKE N'%an%')

This would produce:

Like the other SQL statements, you can also negate this one.

The % wildcard is used to precede or succeed a specific character or a group of characters, that is, any character. If you want to consider only a range of characters from the alphabet, you can include the range in square brackets. To do this, type [, followed by the lowest character of the range, followed by -, followed by the highest character of the range, followed by ]. For example, to consider the range of letters between p and s, you would use N'[p-s]'. Then, either to the left, to the right, or to both sides of this expression, type % to specify whether to include any character or combination of characters before or after the expression. Here is an example:

SELECT     SELECT FirstName, LastName, Sex, City, State
FROM         Students
WHERE     (LastName LIKE N'%[p-s]')

In the case, the result would be a list of students whose last names } with p, q, r, or s. This would produce:

Notice that the list includes only the students whose last names } with a letter between p and s.

As opposed to considering the characters that are in a specific range, to specify a character or a range of characters that must NOT be considered, use the ^ character inside the square brackets but before the desired range. Here is an example:

SELECT     SELECT FirstName, LastName, Sex, City, State
FROM         Students
WHERE     (LastName LIKE N'%[^p-r]')

The result would be a list of students whose last } with a letter other than p, q, r, or s.

Once again, remember that you can negate an expression by preceding it with NOT. Note that if you negate an expression that include ^, you would get the same result as not using ^.