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.
 

3 thoughts on “ASCII and UNICODE Pattern Matching by Using LIKE in Sql Server”

  1. Hi Hirendra,

     

    When i Insert “अंकशास्त्र” in Col1 and write Sql as follows

     

    Select * From Test1 where rtrim(First) like ‘%अंकशास्त्र’

    Select * From Test1 where rtrim(First) like ‘%अंकशास्त्र%’

    Select * From Test1 where First like ‘%अंकशास्त्र’

     

    Nothing Works……….

    please suggest

     

    Thanks

    Ankur

  2. Dear Ankur

    Thanks for interest….

    You need to place N at the beginning of the search pattern like this :

    Select * From Test1 where rtrim(First) like N‘%अंकशास्त्र’

    At each time when you deal with Unicode data in Sql Server, you must prefix the Unicode string with N. When SQL Server converts a Unicode string without the N prefix with Unicode to the SQL Server database’s code page, any characters in the Unicode string that do not exist in the SQL Server code page will be lost. This is same case with your query, some characters in your string ‘अंकशास्त्र’ do not exist in Sql server code page….

    thanks


Comments are closed.