Case Sensitive SQL Query Search and Joining

 

 

This article gives you a total overall idea related to collation. It not only contains definition but also contains SQL comparisons and joins with different type collation table in different database.

So let's start

Collation is defined by a set of rules that determines how the data is stored and compare. There are for types of collation and they are

1.    Case sensitive: if "A" and "a" is treated as same way, then it is case in-sensitive. And id "A" and "a" are treated as different way than it is case sensitive. As the ASCII code of both letters are different. Capital "A" takes 65 and small "a" takes 97.

2.    Accent sensitivity: if "a" and "á" treated as same way then it is accent in-sensitive and if treated differently than it is accent sensitive. The ASCII code of "a" is 97 and "á" is 225.

3.    Kana Sensitivity: When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.

4.    Width sensitivity: When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently than it is width sensitive.

Example of database creation, with different collations is mentioned bellow

USE MASTER

GO

CREATE DATABASE BIN COLLATE LATIN1_GENERAL_BIN

GO

CREATE DATABASE CI_AI_KS COLLATE LATIN1_GENERAL_CI_AI_KS

GO

CREATE DATABASE CS_AS_KS_WS COLLATE LATIN1_GENERAL_CS_AS_KS_WS

GO

A table objects creation example with different collation

CREATE TABLE Mytable

   ([colu]        CHAR(10) COLLATE Albanian_CI_AI_KS_WS NULL,

    [Maydate]     CHAR(8)  COLLATE Korean_Wansung_Unicode_CS_AS_KS NOT NULL ,

    [Risk_Rating] CHAR(2)  COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL)

As a SQL server developer you have to face a situation to compare the two columns of different table having different collation.  For Example you are joining two table which support different collations. If you are not careful it gives you an error msg.

To illustrate my points here I give u a simple example of case sensitive collation.

 

CREATE TABLE Mytable

            (Sname CHAR(100) NOT NULL)

           

INSERT INTO Mytable (Sname)

       VALUES ('JOYDEEP'),('TUHIN'),('PALASH'),('SANGRAM')

      

-- Get the result    

SELECT * FROM Mytable WHERE Sname='joydeep'   

 

To make the query case sensitive uses this.

-- To make the query case sensitive

SELECT * FROM Mytable WHERE Sname COLLATE Latin1_General_CS_AS='joydeep'

--  Now

SELECT * FROM Mytable WHERE Sname COLLATE Latin1_General_CS_AS='JOYDEEP'   

To find the collation of any table use this

EXEC sp_help Mytable

 

Now I am giving an example to join two tables from different database with different collations.

If we perform this join it gives an error

SELECT ProductCode, ProductResalePrice

FROM   [database1].[dbo].[PRODUCTS]

       INNER JOIN [database2].[dbo].[items] ON ProductCode = [items].itemCode

Error:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

We have to first know what type of collation both of the databases used to further precede the joining operations.

SELECT DATABASEPROPERTYEX('database1', 'Collation') SQLCollation;

SELECT DATABASEPROPERTYEX('database2', 'Collation') SQLCollation;

Now we can

SELECT ProductCode, ProductResalePrice

FROM   [database1].[dbo].[PRODUCTS]

       INNER JOIN [database2].[dbo].[items]

       ON ProductCode COLLATE SQL_Latin1_General_CP1_CI_AS = [items].itemCode

 

I think that the article is quite informative and thanking you to provide your valuable time on it.

 

Posted by: JOYDEEP DAS