[Reprinted] SQL server T-SQL two methods to distinguish string case

Original article: http://blog.csdn.net/ylqmf/article/details/4972034

Method 1

Write a function

 

-- String comparison functions that are case sensitive to strings
Create function [dbo]. [StrComp] (@ Str1 NVARCHAR (50), @ Str2 NVARCHAR (50 ))
Returns integer--1 is equal, and its return value is not equal.
AS
BEGIN
Declare @ hashcode1 varbinary (32)
Declare @ hashcode2 varbinary (32)
Declare @ result int

Select @ hashcode1 = HashBytes ('md5', @ Str1)
Select @ hashcode2 = HashBytes ('md5', @ Str2)

If @ hashcode1 = @ hashcode2
Begin
Set @ result = 1;
End
Else
Begin
Set @ result = 0;
End
Return @ result;
End

 

Use

Declare @ Str1 nvarchar (50)
Declare @ Str2 nvarchar (50)

Set @ Str1 = 'abc'
Set @ Str2 = 'abc'

Select [dbo]. [StrComp] (@ Str1, @ Str2)

 

Result

====================

0

 

 

 

Method 2

 

Use the collate clause to define the conversion rules of strings as Chinese_PRC_CS_AS_WS

 

Use

Declare @ Str1 nvarchar (50)
Declare @ Str2 nvarchar (50)

Set @ Str1 = 'abc'
Set @ Str2 = 'abc'

If @ Str1 collate Chinese_PRC_CS_AS_WS = @ Str2
Begin
Select 1
End
Else
Begin
Select 0
End

 

Result

==============

0