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

Posted by Karthikanbarasan under Sql Server on 2/10/2011 | Points: 10 | Views : 72246 | Status : [Member] [Moderator] [Microsoft_MVP] [MVP] | Replies : 7

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Can any one explain the use of COLLATE keyword in sql with an example

Thanks
Karthik
www.f5Debug.net

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

0	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

0	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

0	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

0	hi pandiyan what is this? SQL_Latin1_General_CP1_CS_AS how to find this instance? Mark as Answer if its helpful to you Kumaraspcode2009@gmail.com Karthikanbarasan, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts