SQL Server：将所有大写转换为正确大小写/标题大小写
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/230138/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server: Make all UPPER case to Proper Case/Title Case
提问by RyanKeeter
I have a table that was imported as all UPPER CASE and I would like to turn it into Proper Case. What script have any of you used to complete this?
我有一个表格全部是大写，我想把它变成正确的大小写。你们中有人用什么脚本来完成这个？
采纳答案by Galwegian
Here's a UDF that will do the trick...
这是一个可以解决问题的UDF...
create function ProperCase(@Text as varchar(8000))
returns varchar(8000)
as
begin
declare @Reset bit;
declare @Ret varchar(8000);
declare @i int;
declare @c char(1);
if @Text is null
return null;
select @Reset = 1, @i = 1, @Ret = '';
while (@i <= len(@Text))
select @c = substring(@Text, @i, 1),
@Ret = @Ret + case when @Reset = 1 then UPPER(@c) else LOWER(@c) end,
@Reset = case when @c like '[a-zA-Z]' then 0 else 1 end,
@i = @i + 1
return @Ret
end
You will still have to use it to update your data though.
尽管如此，您仍然必须使用它来更新您的数据。
回答by Tomalak
This function:
这个功能：
- "Proper Cases" all "UPPER CASE" words that are delimited by white space
- leaves "lower case words" alone
- works properly even for non-English alphabets
- is portable in that it does not use fancy features of recent SQL server versions
- can be easily changed to use NCHAR and NVARCHAR for unicode support,as well as any parameter length you see fit
- white space definition can be configured
- "Proper Cases" 所有由空格分隔的 "UPPER CASE" 单词
- 单独留下“小写单词”
- 即使对于非英文字母也能正常工作
- 是可移植的，因为它不使用最近 SQL Server 版本的花哨功能
- 可以轻松更改为使用 NCHAR 和 NVARCHAR 来支持 unicode，以及您认为合适的任何参数长度
- 可以配置空白定义
CREATE FUNCTION ToProperCase(@string VARCHAR(255)) RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @i INT -- index
DECLARE @l INT -- input length
DECLARE @c NCHAR(1) -- current char
DECLARE @f INT -- first letter flag (1/0)
DECLARE @o VARCHAR(255) -- output string
DECLARE @w VARCHAR(10) -- characters considered as white space
SET @w = '[' + CHAR(13) + CHAR(10) + CHAR(9) + CHAR(160) + ' ' + ']'
SET @i = 1
SET @l = LEN(@string)
SET @f = 1
SET @o = ''
WHILE @i <= @l
BEGIN
SET @c = SUBSTRING(@string, @i, 1)
IF @f = 1
BEGIN
SET @o = @o + @c
SET @f = 0
END
ELSE
BEGIN
SET @o = @o + LOWER(@c)
END
IF @c LIKE @w SET @f = 1
SET @i = @i + 1
END
RETURN @o
END
Result:
结果：
dbo.ToProperCase('ALL UPPER CASE and SOME lower ?? ?? üü éé ?? ?? ??')
-----------------------------------------------------------------
All Upper Case and Some lower ?? ?? üü éé ?? Cc ??
回答by Richard Sayakanit
UPDATE titles
SET title =
UPPER(LEFT(title, 1)) +
LOWER(RIGHT(title, LEN(title) - 1))
回答by Greg Beech
If you can enable the CLR in SQL Server (requires 2005 or later) then you could create a CLR functionthat uses the TextInfo.ToTitleCase built-in functionwhich would allow you to create a culture-aware way of doing this in only a few lines of code.
如果您可以在 SQL Server 中启用 CLR（需要 2005 或更高版本），那么您可以创建一个使用TextInfo.ToTitleCase 内置函数的 CLR 函数，这将允许您创建一种文化感知方式，只需几个代码行。
回答by Harmeet Singh Bhamra
I know this is late post in this thread but, worth looking. This function works for me ever time. So thought of sharing it.
我知道这是该线程中的后期帖子，但是值得一看。这个功能每次都对我有用。于是想到了分享。
CREATE FUNCTION [dbo].[fnConvert_TitleCase] (@InputString VARCHAR(4000) )
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @OutputString VARCHAR(255)
SET @OutputString = LOWER(@InputString)
SET @Index = 2
SET @OutputString = STUFF(@OutputString, 1, 1,UPPER(SUBSTRING(@InputString,1,1)))
WHILE @Index <= LEN(@InputString)
BEGIN
SET @Char = SUBSTRING(@InputString, @Index, 1)
IF @Char IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&','''','(')
IF @Index + 1 <= LEN(@InputString)
BEGIN
IF @Char != ''''
OR
UPPER(SUBSTRING(@InputString, @Index + 1, 1)) != 'S'
SET @OutputString =
STUFF(@OutputString, @Index + 1, 1,UPPER(SUBSTRING(@InputString, @Index + 1, 1)))
END
SET @Index = @Index + 1
END
RETURN ISNULL(@OutputString,'')
END
Test calls:
测试调用：
select dbo.fnConvert_TitleCase(Upper('?? ?? üü éé ?? ?? ??')) as test
select dbo.fnConvert_TitleCase(upper('Whatever the mind of man can conceive and believe, it can achieve. – Napoleon hill')) as test
Results:
结果：
回答by Alansoft
I am a little late in the game, but I believe this is more functional and it works with any language, including Russian, German, Thai, Vietnamese etc. It will make uppercase anything after ' or - or . or ( or ) or space (obviously :).
我在游戏中有点晚了，但我相信这更实用，它适用于任何语言，包括俄语、德语、泰语、越南语等。它会使 ' 或 - 或 之后的任何内容大写。或（或）或空格（显然是:)。
CREATE FUNCTION [dbo].[fnToProperCase]( @name nvarchar(500) )
RETURNS nvarchar(500)
AS
BEGIN
declare @pos int = 1
, @pos2 int
if (@name <> '')--or @name = lower(@name) collate SQL_Latin1_General_CP1_CS_AS or @name = upper(@name) collate SQL_Latin1_General_CP1_CS_AS)
begin
set @name = lower(rtrim(@name))
while (1 = 1)
begin
set @name = stuff(@name, @pos, 1, upper(substring(@name, @pos, 1)))
set @pos2 = patindex('%[- ''.)(]%', substring(@name, @pos, 500))
set @pos += @pos2
if (isnull(@pos2, 0) = 0 or @pos > len(@name))
break
end
end
return @name
END
GO
回答by Kenjamarticus
If you're in SSIS importing data that has mixed cased and need to do a lookup on a column with proper case, you'll notice that the lookup fails where the source is mixed and the lookup source is proper. You'll also notice you can't use the right and left functions is SSIS for SQL Server 2008r2 for derived columns. Here's a solution that works for me:
如果您在 SSIS 中导入混合大小写的数据，并且需要在具有正确大小写的列上进行查找，您会注意到在混合源且查找源正确的情况下查找失败。您还会注意到您不能将右侧和左侧功能是 SSIS for SQL Server 2008r2 用于派生列。这是一个对我有用的解决方案：
UPPER(substring(input_column_name,1,1)) + LOWER(substring(input_column_name, 2, len(input_column_name)-1))
回答by Dennis Allen
Here is a version that uses a sequence or numbers table rather than a loop. You can modify the WHERE clause to suite your personal rules for when to convert a character to upper case. I have just included a simple set that will upper case any letter that is proceeded by a non-letter with the exception of apostrophes. This does how ever mean that 123apple would have a match on the "a" because "3" is not a letter. If you want just white-space (space, tab, carriage-return, line-feed), you can replace the pattern '[^a-z]'with '[' + Char(32) + Char(9) + Char(13) + Char(10) + ']'.
这是一个使用序列或数字表而不是循环的版本。您可以修改 WHERE 子句，以适应您何时将字符转换为大写的个人规则。我刚刚包含了一个简单的集合，它将大写任何以非字母开头的字母，撇号除外。这确实意味着 123apple 将匹配“a”，因为“3”不是字母。如果你只想要空白（空格、制表符、回车、换行），你可以'[^a-z]'用'[' + Char(32) + Char(9) + Char(13) + Char(10) + ']'.
CREATE FUNCTION String.InitCap( @string nvarchar(4000) ) RETURNS nvarchar(4000) AS
BEGIN
-- 1. Convert all letters to lower case
DECLARE @InitCap nvarchar(4000); SET @InitCap = Lower(@string);
-- 2. Using a Sequence, replace the letters that should be upper case with their upper case version
SELECT @InitCap = Stuff( @InitCap, n, 1, Upper( SubString( @InitCap, n, 1 ) ) )
FROM (
SELECT (1 + n1.n + n10.n + n100.n + n1000.n) AS n
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS n1
CROSS JOIN (SELECT 0 AS n UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40 UNION SELECT 50 UNION SELECT 60 UNION SELECT 70 UNION SELECT 80 UNION SELECT 90) AS n10
CROSS JOIN (SELECT 0 AS n UNION SELECT 100 UNION SELECT 200 UNION SELECT 300 UNION SELECT 400 UNION SELECT 500 UNION SELECT 600 UNION SELECT 700 UNION SELECT 800 UNION SELECT 900) AS n100
CROSS JOIN (SELECT 0 AS n UNION SELECT 1000 UNION SELECT 2000 UNION SELECT 3000) AS n1000
) AS Sequence
WHERE
n BETWEEN 1 AND Len( @InitCap )
AND SubString( @InitCap, n, 1 ) LIKE '[a-z]' /* this character is a letter */
AND (
n = 1 /* this character is the first `character` */
OR SubString( @InitCap, n-1, 1 ) LIKE '[^a-z]' /* the previous character is NOT a letter */
)
AND (
n < 3 /* only test the 3rd or greater characters for this exception */
OR SubString( @InitCap, n-2, 3 ) NOT LIKE '[a-z]''[a-z]' /* exception: The pattern <letter>'<letter> should not capatolize the letter following the apostrophy */
)
-- 3. Return the modified version of the input
RETURN @InitCap
END
回答by Lee
It would make sense to maintain a lookup of exceptions to take care of The von Neumann's, McCain's, DeGuzman's, and the Johnson-Smith's.
维护异常查找以处理 von Neumann's、McCain's、DeGuzman's 和 Johnson-Smith's 是有意义的。
回答by philipnye
A slight modification to @Galwegian's answer - which turns e.g. St Elizabeth'sinto St Elizabeth'S.
对@Galwegian 的回答稍作修改 - 例如St Elizabeth's变成St Elizabeth'S.
This modification keeps apostrophe-s as lowercase where the s comes at the end of the string provided or the s is followed by a space (and only in those circumstances).
此修改将撇号-s 保持为小写，其中 s 位于所提供字符串的末尾或 s 后跟一个空格（并且仅在这些情况下）。
create function properCase(@text as varchar(8000))
returns varchar(8000)
as
begin
declare @reset int;
declare @ret varchar(8000);
declare @i int;
declare @c char(1);
declare @d char(1);
if @text is null
return null;
select @reset = 1, @i = 1, @ret = '';
while (@i <= len(@text))
select
@c = substring(@text, @i, 1),
@d = substring(@text, @i+1, 1),
@ret = @ret + case when @reset = 1 or (@reset=-1 and @c!='s') or (@reset=-1 and @c='s' and @d!=' ') then upper(@c) else lower(@c) end,
@reset = case when @c like '[a-za-z]' then 0 when @c='''' then -1 else 1 end,
@i = @i + 1
return @ret
end
It turns:
事实证明：
st elizabeth'sintoSt Elizabeth'so'keefeintoO'Keefeo'sullivanintoO'Sullivan
st elizabeth's进入St Elizabeth'so'keefe进入O'Keefeo'sullivan进入O'Sullivan
Others' comments that different solutions are preferable for non-English input remain the case.
其他人认为不同的解决方案更适合非英语输入的评论仍然如此。
