SQL Server：使所有大写字母变为正确的字母/标题字母

Question

我有一个作为所有大写字母导入的表，我想将其转换为大写字母。你们使用什么脚本来完成此任务？

Answer 1

这是可以解决问题的UDF ...create function ProperCase(@Text as varchar(8000))returns varchar(8000)asbegin&nbsp; declare @Reset bit;&nbsp; declare @Ret varchar(8000);&nbsp; declare @i int;&nbsp; declare @c char(1);&nbsp; if @Text is null&nbsp; &nbsp; return null;&nbsp; select @Reset = 1, @i = 1, @Ret = '';&nbsp; while (@i <= len(@Text))&nbsp; &nbsp; select @c = substring(@Text, @i, 1),&nbsp; &nbsp; &nbsp; @Ret = @Ret + case when @Reset = 1 then UPPER(@c) else LOWER(@c) end,&nbsp; &nbsp; &nbsp; @Reset = case when @c like '[a-zA-Z]' then 0 else 1 end,&nbsp; &nbsp; &nbsp; @i = @i + 1&nbsp; return @Retend但是，您仍然必须使用它来更新数据。

Answer 2

我玩游戏有点晚了，但是我相信这是更实用的功能，并且可以与任何语言一起使用，包括俄语，德语，泰语，越南语等。它将在'或-或之后加上大写字母。或（或）或空格（显然是：）。CREATE FUNCTION [dbo].[fnToProperCase]( @name nvarchar(500) )RETURNS nvarchar(500)ASBEGINdeclare @pos&nbsp; &nbsp; int = 1&nbsp; &nbsp; &nbsp; , @pos2&nbsp; &nbsp;intif (@name <> '')--or @name = lower(@name) collate SQL_Latin1_General_CP1_CS_AS or @name = upper(@name) collate SQL_Latin1_General_CP1_CS_AS)begin&nbsp; &nbsp; set @name = lower(rtrim(@name))&nbsp; &nbsp; while (1 = 1)&nbsp; &nbsp; begin&nbsp; &nbsp; &nbsp; &nbsp; set @name = stuff(@name, @pos, 1, upper(substring(@name, @pos, 1)))&nbsp; &nbsp; &nbsp; &nbsp; set @pos2 = patindex('%[- ''.)(]%', substring(@name, @pos, 500))&nbsp; &nbsp; &nbsp; &nbsp; set @pos += @pos2&nbsp; &nbsp; &nbsp; &nbsp; if (isnull(@pos2, 0) = 0 or @pos > len(@name))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; break&nbsp; &nbsp; endendreturn @nameENDGO