ASCII and UNICODE Pattern Matching by Using LIKE in Sql Server

 
In Sql server we use the LIKE keyword for searching string, date, or time values with the help of specific user defined pattern. LIKE uses a regular expression to contain the pattern(contains the character string). LIKE support both type ASCII and UNICODE pattern matching. Suppose if we use LIKE for ASCII data(such as CHAR data type) then ASCII pattern matching will be performed and if we use LIKE with UNICODE data(such as NCHAR or NVARCHAR data types) ,UNICODE pattern matching will be performed.

The following example show the result of the ASCII and UNICODE pattern matching with LIKE.
When we use ASCII data with LIKE, trailing blanks are significant and when we use UNICODE data with LIKE trailing blanks are not significant. See this:

Suppose you have a table named tblCode that has following columns:

Col1 nChar(20)
Col2 char(20)

now see the result of the following queries:

select * from tblCode where Col1 Like ‘% Code’

Output:

Col1Col2

no rows returned

select * from tblCode where Col2 Like ‘% Code’

Output:

Col1Col2
Author CodeAuthor Code

One rows returned

But when we use RTRIM function with Col1 then it returns one row see:

select * from tblCode where rtrim(Col1) Like ‘% Code’

Output:

Col1Col2
Author CodeAuthor Code

One rows returned

so this is the difference between ASCII and UNICODE pattern matching in Sql Server.
 

Author: Hirendra Sisodiya

Programmer at PSPL. Can be found on Google Plus.