What is the use of COLLATE in sql server? [Resolved]

Posted by Karthikanbarasan under Sql Server on 2/10/2011 | Points: 10 | Views : 63470 | Status : [Member] [Moderator] [Microsoft_MVP] [MVP] | Replies : 7
Can any one explain the use of COLLATE keyword in sql with an example

Thanks
Karthik
www.f5Debug.net



Responses

Posted by: PandianS on: 2/10/2011 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
Hi

Know about COLLATE:

- Normally we have 4 type of sensitivity on SQL Server (Case, Width, Accent, kanatype)
- If you want to force any one of the sensitivity as goven above then you have to use COLLATE.

Here, I would like to explain about "Case Sensitive"...

One small question for you..

How will you validate the data based on the Case sensitive on the column / expression ?

i.e: "A" is differs from "a" correct ?

If so, How will you do that ?

Here, you have one small workarround...
DECLARE @A	VARCHAR(10)

SELECT @A = 'a'

IF (@A = 'A')
PRINT 'Match'
ELSE
PRINT 'No Match'
Normally the result should be No Match correct ?

But, you will get the result as Match . Because, The sql server engine conpares the expression as Case-Insensitive

So, How will you force to validate Case sensitive ?
DECLARE @A	VARCHAR(10)

SELECT @A = 'a'

IF (@A = 'A' COLLATE SQL_Latin1_General_CP1_CS_AS)
PRINT 'Match'
ELSE
PRINT 'No Match'


Now, you will get the result as No Match

Here, I have validated the expression with Case sensitive collation (SQL_Latin1_General_CP1_CS_AS)

Cheers
www.sqlserverbuddy.blogspot.com

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

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

Posted by: Karthikanbarasan on: 2/10/2011 [Member] [Moderator] [Microsoft_MVP] [MVP] Silver | Points: 25

Up
0
Down
Brilliant Explanation... Thanks!!!

Thanks
Karthik
www.f5Debug.net

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

Posted by: Jatinnahar15 on: 11/7/2012 [Member] Starter | Points: 25

Up
0
Down
PandianS,
Nice explanation || really good....thx sirjiii

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

Posted by: Kuntal.Kr.Patra on: 4/2/2013 [Member] Starter | Points: 25

Up
0
Down
Really nice explanation......

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

Posted by: Vedaraj on: 4/4/2013 [Member] Starter | Points: 25

Up
0
Down
Nice Explanation Pandian

vedaraj

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

Posted by: Hurera1111 on: 4/4/2013 [Member] Starter | Points: 25

Up
0
Down
Nice explanation... :)

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

Posted by: Jayakumars on: 4/4/2013 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi
pandiyan

what is this?
SQL_Latin1_General_CP1_CS_AS

how to find this instance?

Mark as Answer if its helpful to you

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

Login to post response