SQLSERVER中比较字符串的问题

[Anstinus]

'd' = 'd'和'd' = 'D'　结果都是TRUE，如何区分大小写

[saucer]

from
http://www.4guysfromrolla.com/webtech/sqlguru/q022400-1.shtml

"....case-sensitivity on SQL Server is determined at installation time when you choose the sort order. To do true case-sensitive string comparisons, you'll need to either install a case-sensitive sort order, write a custom comparison routine, or quit caring about case-sensitivity in these strings...."

[newly_ignorant]

自己写一个字符串比较函数好了，不过写好了，别忘了贴出来。

[newly_ignorant]

无聊啊，参考一下吧
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

Create FUNCTION dbo.CompareStr
(@chvStr1 varchar(8000) , --分隔符号
@chvStr2 varchar(8000) ) --查询的变量
returns int -- 0相等，1大于,－1小于,-2空
AS
begin
declare @chvTmp1 char(1)
declare @chvTmp2 char(1)

declare @intLen int
declare @intPos int

set @chvStr1 = ltrim(rtrim(@chvStr1))
set @chvStr2 = ltrim(rtrim(@chvStr2))

--数据检查
if @chvStr1 is null or @chvStr2 is null
return -2
--不等
if @chvStr1 > @chvStr2
return 1
if @chvStr1 < @chvStr2
return -1

if @chvStr1 = @chvStr2
begin
set @intLen = len(@chvStr1)
set @intPos = 0
while @intPos < @intLen - 1
begin
set @intPos = @intPos + 1
set @chvTmp1 = substring(@chvStr1,@intPos,1)
set @chvTmp2 = substring(@chvStr2,@intPos,1)
--不等
if ascii(@chvTmp1) > ascii(@chvTmp2)
return 1
if ascii(@chvTmp1) < ascii(@chvTmp2)
return -1
end
--same
if @intPos = @intLen
return 0
end

return -2
end




GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

[newly_ignorant]

等于写错了

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


ALTER FUNCTION dbo.CompareStr
(@chvStr1 varchar(8000) , --分隔符号
@chvStr2 varchar(8000) ) --查询的变量
returns int -- 0相等，1大于,－1小于,-2空
AS
begin
declare @chvTmp1 char(1)
declare @chvTmp2 char(1)

declare @intLen int
declare @intPos int

set @chvStr1 = ltrim(rtrim(@chvStr1))
set @chvStr2 = ltrim(rtrim(@chvStr2))

--数据检查
if @chvStr1 is null or @chvStr2 is null
return -2
--不等
if @chvStr1 > @chvStr2
return 1
if @chvStr1 < @chvStr2
return -1

if @chvStr1 = @chvStr2
begin
set @intLen = len(@chvStr1)
set @intPos = 0
while @intPos < @intLen - 1
begin
set @intPos = @intPos + 1
set @chvTmp1 = substring(@chvStr1,@intPos,1)
set @chvTmp2 = substring(@chvStr2,@intPos,1)
--不等
if ascii(@chvTmp1) > ascii(@chvTmp2)
return 1
if ascii(@chvTmp1) < ascii(@chvTmp2)
return -1
end

end
return 0
end





GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO