Is it possible to search strings in columns assigned with the Unicode datatype in the same way that we search strings in non-Unicode columns?
No, it’s slightly different approach.
Let’s look at what Unicode and non-Unicode datatypes are before we get started with the exercise.
Despite the fact that Unicode and non-Unicode work alike, UNICODE is a standard for character encoding. Modules or character encodings that do not support the Unicode format are referred to as non-Unicode.
SQL Server supports both UNICODE and NON-UNICODE data types:
> Non-Unicode datatypes are CHAR, VARCHAR and TEXT.
> Unicode datatypes are: NCHAR. NVARCHAR and NTEXT.
(“N” stands for ‘National Language Character Set’)
The following are the differences between Unicode and Non-Unicode datatypes:
1) Unicode takes up two bytes of storage, while non-Unicode takes up one.
2) Char and Varchar can hold up to 8000 characters, while Nchar and Nvarchar can only hold
up to 4000.
3) Non-Unicode datatypes are better fit for “US English.” Unicode stores all foreign language
words and strings.
As stated above, Unicode datatypes are useful for storing foreign language words/strings that cannot be searched the way we do for non-Unicode datatype columns.
Let’s begin the exercise.
CREATE TABLE tbSearchNVARCHAR(
IDCol INT,
BookName VARCHAR(MAX),
TxtCol NVARCHAR(MAX));
INSERT INTO tbSearchNVARCHAR VALUES
(10, ‘The Jungle Book’, N’В этой книге 200 страниц.’),
(20, ‘The Magic Book’, N’В этой книге 150 страниц.’)
SELECT * FROM tbSearchNVARCHAR
Let’s try to search the string.
SELECT * FROM tbSearchNVARCHAR WHERE TxtCol = ‘В этой книге 200 страниц.’
SELECT * FROM tbSearchNVARCHAR WHERE TxtCol LIKE ‘%страниц%’
Both the above queries returned no results.
Let’s see how to search the NVARCHAR strings.
SELECT * FROM tbSearchNVARCHAR WHERE TxtCol LIKE N’В этой книге 200 страниц.’
SELECT * FROM tbSearchNVARCHAR WHERE TxtCol LIKE N’%страниц%’
Hope you liked this post.
Please do click on follow button to receive notifications on latest posts.
Big Data and SQL 
One comment