Condition LIKE in SQL Server

The LIKE condition in SQL Server (Transact-SQL) allows the use of wildcards in the WHERE clause in SELECT, INSERT, UPDATE and DELETE statements, used to match patterns.

Syntax LIKE condition

 'biểu thức' LIKE 'mẫu' [ ESCAPE 'Escape_Character' ] 

Variable name or variable value

expression

Character expressions such as columns or information fields.

form

Character formats contain collated samples. Samples can be selected from the table below.

Representative symbol Explain% finding the character string for any length (including length 0) _ finding a character [^] to find any character not contained in the [^] character (eg [ ^ abc] will find any character that is not a, b or c)

Escape Character

Option. Temporarily translates as a skip character, allowing you to check if the characters like% or _ are used literally (in the form of constants), but not as a special character.

For example - use wildcard%

Use the wildcard% in SQL Server's LIKE condition, for example by looking for employees who have them starting with the letter 'B'

 S ELECT * 
 FROM nhanvien 
 WHERE ho LIKE 'B%'; 

You can use% characters in the same string as the example below.

 SELE CT * 
 FROM nhanvien 
 WHER ho LIKE '%0%'; 

The results for the employees in which they contain the letter 'o'.

For example - use wildcard _

Note that the character _ is only used to search for 1 character (length is 1).

 S ELECT * 
 FROM nhanvien 
 WHERE ho LIKE 'Ad_m'; 

In this example, the returned result is the employee with a 4-letter name, where the first two letters are 'Ad' and the last letter is 'm'. For example, Adam, Adem, Adim, Adom .

This is another example

 SELECT * 
 FROM nhanvien 
 WHERE so_nhanvien LIKE '123_'; 

You are looking for the number of employees that only 3 in 4 digits. In the above example, the result returned includes 10 records, missing values ​​are from 0 to 9: 1230, 1231, 1232 .

For example - use wildcard []

Note that what's enclosed in square brackets is the character you want and match the pattern.

 SELECT * 
 FROM nhanvien 
 WHERE ten LIKE 'Sm[iy]th'; 

The result returned in this example is the employees whose name has 5 letters, of which the first two letters are 'Sm' and the last two letters are 'th', the middle letter can be 'i' or 'y'. So the result might be Smith or Smyth.

For example - use wildcard [^]

What is in square brackets are the characters you don't want to match the pattern.

 S ELECT * 
 FROM nhanvien 
 WHERE ten LIKE 'Smy[^iy]th'; 

As a result, the employee name has 5 letters, in which the first two letters are 'Sm', the last two words are 'th' and the middle word is not 'i' nor 'y'. The result could be Smath, Smeth .

For example - use the NOT operator

This is how to use the NOT operator in SQL Server with wildcards. You can find the employee surname that does not start with the letter 'B' with the LIKE condition.

 SE LECT * 
 FROM nhanvien 
 WHERE ho NOT LIKE 'B%'; 

By setting the NOT operator before the LIKE condition, you find the employee family that does not start with 'B'.

For example - use the character to skip Escape Character

Using skip characters is important when comparing patterns, to avoid misunderstanding special characters when you want to use it as a constant value.

For example, if you want to search for characters a% or a_ for example.

Note that only the characters can be defined by 1 character (length is 1).

 SELEC T * 
 FROM nhanvien 
 WHERE secret_hint LIKE '123!%455' ESCAPE '!'; 

This command will return employees whose secret_hint is 123% 455. The% character is no longer used with the previous meaning but as a normal character.

This is a more complex example.

 SELE CT * 
 FROM nhanvien 
 WHERE secret_hint LIKE 'H %!%' ESCAPE '!'; 

The return result of the LIKE condition is that the employees with secret_hint start with 'H' and end with '%', such as 'Help%'.

You can use the ignore character with the whole character _ in the LIKE condition.

 SEL ECT * 
 FROM nhanvien 
 WHERE secret_hint LIKE 'H%!_' ESCAPE '!'; 

This example returns employees with secret_hint starting with 'H' and ending with '_', such as 'Help_'.

Previous lesson: IS NOT NULL condition in SQL Server

Next article: Conditions NOT in SQL Server