What is the purpose of using COLLATE in a query ?

Please look at this

http://technet.microsoft.com/en-us/library/ms184391.aspx

Thank you for posting at Dotnetfunda
[Administrator]

Pkanwar, if this helps please login to Mark As Answer. | Alert Moderator

Collations: used to determine how to store, compare, and sort data.
1) A collation defines the sort order for data by using code pages. The code pages are associated with the collation specified for the data. For non-unicode data types, the collations must use a code page to decide the sort order of data.

2) Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.

3) By default SQL Server stores data as Case-Insensitive. If you wish to do Case-Sensitive Search against data we must convert the collation type to case sensitive by using COLLATE clause

4) We can set the collation type at Server-level, Database-level, table-level and field-level

5) You can also use the COLLATE in the ORDER BY clause to explicitly state which collation should be used to order the result set

Sample Example:

create table testCollation 
(LatinCol nvarchar(15) COLLATE latin1_general_cs_as,
FreanchCol nvarchar(15) COLLATE french_100_cs_as)

INSERT INTO testcollation values('A','B'),('A', 'b'), ('A', 'A')

select LatinCol, FreanchCol
from testcollation
where LatinCol=FreanchCol

select LatinCol, FreanchCol
from testcollation
where LatinCol=FreanchCol COLLATE french_100_cs_as

DROP TABLE testcollation

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Pkanwar, if this helps please login to Mark As Answer. | Alert Moderator

Click on "Mark as Answer " link

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Pkanwar, if this helps please login to Mark As Answer. | Alert Moderator

hi.. this is your answer

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.

COLLATE is a clause that can be applied to a database definition or a column definition to define the collation, or to a character string expression to apply a collation cast.

SAMIR
Sr. Software Engineer

Pkanwar, if this helps please login to Mark As Answer. | Alert Moderator