SQL 内置函数将每个单词的第一个字母大写
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5164201/
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
Built-in function to capitalise the first letter of each word
提问by girish
I don't want to create custom function for that if there is any already exists in SQL Server
如果 SQL Server 中已经存在任何自定义函数，我不想为此创建自定义函数
Input string: This is my string to convert
Expected output: This Is My String To Convert
输入字符串：This is my string to convert
预期输出：This Is My String To Convert
回答by ashish.chotalia
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[InitCap] ( @InputString varchar(4000) )
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @PrevChar CHAR(1)
DECLARE @OutputString VARCHAR(255)
SET @OutputString = LOWER(@InputString)
SET @Index = 1
WHILE @Index <= LEN(@InputString)
BEGIN
SET @Char = SUBSTRING(@InputString, @Index, 1)
SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
ELSE SUBSTRING(@InputString, @Index - 1, 1)
END
IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
BEGIN
IF @PrevChar != '''' OR UPPER(@Char) != 'S'
SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))
END
SET @Index = @Index + 1
END
RETURN @OutputString
END
Declare @str nvarchar(100)
SET @str = 'my string to convert'
SELECT @str = [dbo].[InitCap](@str)
SELECT @str
回答by sgokhales
AFAIK, SQL Server has no built-in function for this.
You have to write custom function for it.
AFAIK，SQL Server 没有内置功能。
您必须为它编写自定义函数。
Try this.
尝试这个。
CREATE FUNCTION [dbo].[CamelCase]
(@Str varchar(8000))
RETURNS varchar(8000) AS
BEGIN
DECLARE @Result varchar(2000)
SET @Str = LOWER(@Str) + ' '
SET @Result = ''
WHILE 1=1
BEGIN
IF PATINDEX('% %',@Str) = 0 BREAK
SET @Result = @Result + UPPER(Left(@Str,1))+
SubString (@Str,2,CharIndex(' ',@Str)-1)
SET @Str = SubString(@Str,
CharIndex(' ',@Str)+1,Len(@Str))
END
SET @Result = Left(@Result,Len(@Result))
RETURN @Result
END
Output :
输出 ：
Input String : 'microSoft sql server'
Output String : 'Microsoft Sql Server'
回答by Vincent Vancalbergh
I'd have to go with "No, that does not exist". This based on several years of perusing the available string-functions in T-SQL and some pretty recent 5-day courses in SQL Server 2008 R2.
我不得不说“不，那不存在”。这是基于多年来对 T-SQL 中可用字符串函数的仔细研究以及最近在 SQL Server 2008 R2 中进行的一些为期 5 天的课程。
Of course, I still could be wrong :).
当然，我仍然可能是错的:)。
回答by MikeTeeVee
If the goal of your operation is to prettify strings of Names then proper capitalization could be defined as the first letter of each word separated by non-alphabet characters.
如果您的操作目标是美化名称字符串，则可以将正确的大写定义为每个单词的第一个字母，由非字母字符分隔。
Other solutions do not take into account:
其他解决方案不考虑：
- Preserving spacing (especially trailing spaces).
- Preserving NULL, empty-string, or a string of just spaces.
- Handling more than just spaces (e.g. dashes, commas, underscores, etc...)
- Handling more than one non-alpha character between words/tokens.
- Handling exceptions (e.g. McDonald or III like in "James William Bottomtooth the III").
- 保留间距（尤其是尾随空格）。
- 保留 NULL、空字符串或仅包含空格的字符串。
- 处理的不仅仅是空格（例如破折号、逗号、下划线等...）
- 在单词/标记之间处理多个非字母字符。
- 处理异常（例如“James William Bottomtooth the III”中的 McDonald 或 III）。
Note: My solution does not handle exceptions.
If you are very concerned about those, then I suggest writing a CLR C# assembly for those as it will be tricky, and strings are an area where C# excels.
Another solution on here tries to account for this, but it would still take "ivan terrible the iv" and output "**IV***an Terrible The IV*".
注意：我的解决方案不处理异常。
如果您非常关心这些，那么我建议为那些编写 CLR C# 程序集，因为它会很棘手，而字符串是 C# 擅长的领域。
这里的另一个解决方案试图解决这个问题，但它仍然需要“ ivan可怕的 iv”并输出“**IV***an Terrible The IV*”。
This is the function I came up with:
这是我想出的功能：
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fs_PascalCase]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fs_PascalCase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fs_PascalCase]
(
@Text nVarChar(MAX)
)
RETURNS nVarChar(MAX)
AS
BEGIN
SET @Text = LOWER(@Text)--This step is optional. Keep if you want the code below to control all casing. - 11/26/2013 - MCR.
DECLARE @New nVarChar(MAX) = (CASE WHEN @Text IS NULL THEN NULL ELSE '' END)--Still return null when source is null. - 11/26/2013 - MCR.
DECLARE @Len Int = LEN(REPLACE(@Text, ' ', '_'))--If you want to count/keep trailing-spaces, you MUST use this!!! - 11/26/2013 - MCR.
DECLARE @Index Int = 1--Sql-Server is 1-based, not 0-based.
WHILE (@Index <= @Len)
IF (SUBSTRING(@Text, @Index, 1) LIKE '[^a-z]' AND @Index + 1 <= @Len)--If not alpha and there are more character(s).
SELECT @New = @New + UPPER(SUBSTRING(@Text, @Index, 2)), @Index = @Index + 2
ELSE
SELECT @New = @New + SUBSTRING(@Text, @Index, 1) , @Index = @Index + 1
--If @Text is null, then @Len will be Null, and everything will be null.
--If @Text is '', then (@Len - 1) will be -1, so ABS() it to use 1 instead, which will still return ''.
RETURN ( UPPER(LEFT(@New, 1)) + RIGHT(@New, ABS(@Len - 1)) )
END
GO
You would call it like so:
你会这样称呼它：
SELECT dbo.fs_PascalCase(NULL)[Null],
dbo.fs_PascalCase('')[EmptyString],
dbo.fs_PascalCase('hello how are-you TODAY ')[LongString]
The output will look like this:
输出将如下所示：
回答by Narnian
My Strategy
我的策略
- If the name is already in mixed case, trust that it's right.
- If the name is not in mixed case, then do the following:
- Trim up the name to eliminate white space
- Account for the names that start with “Mc” like “McDavid”
- Account for names with apostrophes like O'Reilly
- Account for hyphenated names (married names) “Anderson-Johnson”
- Account for multiple word names like “La Russa”
- Make sure suffixes included in the names field are capitalized appropriately
- 如果名称已经混合大小写，请相信它是正确的。
- 如果名称不是大小写混合，则执行以下操作：
- 修剪名称以消除空白
- 考虑以“Mc”开头的名称，例如“McDavid”
- 考虑像 O'Reilly 这样带有撇号的名字
- 考虑连字符名称（已婚姓名）“Anderson-Johnson”
- 考虑多个单词名称，例如“La Russa”
- 确保名称字段中包含的后缀正确大写
The Code
编码
Here's my original post on this: Converting String to Camel Case in SQL Server
这是我关于此的原始帖子： Converting String to Camel Case in SQL Server
CREATE FUNCTION [dbo].[GetCamelCaseName]
(
@Name varchar(50)
)
RETURNS VARCHAR(50) WITH SCHEMABINDING
AS
BEGIN
-- Declare the return variable here
DECLARE @NameCamelCase VARCHAR(50)
-- This is determining whether or not the name is in camel case already (if the 1st character is uppercase
-- and the third is lower (because the 2nd could be an apostrophe). To do this, you have to cast the
-- character as varbinary and compare it with the upper case of the character cast as varbinary.
IF (CAST(SUBSTRING(@Name, 1,1) as varbinary) = CAST(SUBSTRING(UPPER(@Name), 1, 1) as varbinary)
AND ((CAST(SUBSTRING(@Name, 2,1) as varbinary) = CAST(SUBSTRING(LOWER(@Name), 2, 1) as varbinary)
AND SUBSTRING(@Name, 2,1) != '''')
or
(CAST(SUBSTRING(@Name, 4,1) as varbinary) = CAST(SUBSTRING(LOWER(@Name), 4, 1) as varbinary)
AND SUBSTRING(@Name, 2,1) = '''')))
BEGIN
SELECT @NameCamelCase = RTRIM(LTRIM(@Name))
SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' sr', ' Sr')
SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' jr', ' Jr')
SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' ii', ' II')
SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' iii', ' III')
SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' DE ', ' de ')
SELECT @NameCamelCase = REPLACE(@NameCamelCase, 'macdonald', 'MacDonald')
if (@NameCamelCase LIKE '% iv') -- avoid changing "Ivan" to "IVan"
SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' iv', ' IV')
if ((@NameCamelCase = 'i') or (@NameCamelCase = 'ii') or (@NameCamelCase = 'iii') or (@NameCamelCase = 'iv'))
SELECT @NameCamelCase = UPPER(@NameCamelCase)
RETURN @NameCamelCase
END
ELSE
BEGIN
SELECT @NameCamelCase = RTRIM(LTRIM(@Name))
-- "Mc-"
SELECT @NameCamelCase =
CASE
WHEN @Name LIKE 'mc%'
THEN UPPER(SUBSTRING(@Name, 1, 1)) + LOWER(SUBSTRING(@Name, 2, 1)) + UPPER(SUBSTRING(@Name, 3, 1)) + LOWER(SUBSTRING(@Name, 4, 47))
ELSE
UPPER(SUBSTRING(@Name, 1, 1)) + LOWER(SUBSTRING(@Name, 2, 49))
END
-- Apostrophes
SELECT @NameCamelCase =
CASE
WHEN @NameCamelCase LIKE '%''%'
THEN SUBSTRING(@NameCamelCase, 1, CHARINDEX('''', @NameCamelCase) - 1) + '''' + UPPER(SUBSTRING(@NameCamelCase, CHARINDEX('''', @NameCamelCase) + 1, 1)) + SUBSTRING(@NameCamelCase, CHARINDEX('''', @NameCamelCase) + 2, 50)
ELSE
@NameCamelCase
END
-- Hyphenated names (do it twice to account for double hyphens)
SELECT @NameCamelCase =
CASE
WHEN @NameCamelCase LIKE '%-%'
THEN SUBSTRING(@NameCamelCase, 1, CHARINDEX('-', @NameCamelCase) - 1) + '^' + UPPER(SUBSTRING(@NameCamelCase, CHARINDEX('-', @NameCamelCase) + 1, 1)) + SUBSTRING(@NameCamelCase, CHARINDEX('-', @NameCamelCase) + 2, 50)
ELSE
@NameCamelCase
END
SELECT @NameCamelCase =
CASE
WHEN @NameCamelCase LIKE '%-%'
THEN SUBSTRING(@NameCamelCase, 1, CHARINDEX('-', @NameCamelCase) - 1) + '^' + UPPER(SUBSTRING(@NameCamelCase, CHARINDEX('-', @NameCamelCase) + 1, 1)) + SUBSTRING(@NameCamelCase, CHARINDEX('-', @NameCamelCase) + 2, 50)
ELSE
@NameCamelCase
END
SELECT @NameCamelCase = REPLACE(@NameCamelCase, '^', '-')
-- Multiple word names (do it twice to account for three word names)
SELECT @NameCamelCase =
CASE
WHEN @NameCamelCase LIKE '% %'
THEN SUBSTRING(@NameCamelCase, 1, CHARINDEX(' ', @NameCamelCase) - 1) + '?' + UPPER(SUBSTRING(@NameCamelCase, CHARINDEX(' ', @NameCamelCase) + 1, 1)) + SUBSTRING(@NameCamelCase, CHARINDEX(' ', @NameCamelCase) + 2, 50)
ELSE
@NameCamelCase
END
SELECT @NameCamelCase =
CASE
WHEN @NameCamelCase LIKE '% %'
THEN SUBSTRING(@NameCamelCase, 1, CHARINDEX(' ', @NameCamelCase) - 1) + '?' + UPPER(SUBSTRING(@NameCamelCase, CHARINDEX(' ', @NameCamelCase) + 1, 1)) + SUBSTRING(@NameCamelCase, CHARINDEX(' ', @NameCamelCase) + 2, 50)
ELSE
@NameCamelCase
END
SELECT @NameCamelCase = REPLACE(@NameCamelCase, '?', ' ')
-- Names in Parentheses
SELECT @NameCamelCase =
CASE
WHEN @NameCamelCase LIKE '%(%'
THEN SUBSTRING(@NameCamelCase, 1, CHARINDEX('(', @NameCamelCase) - 1) + '(' + UPPER(SUBSTRING(@NameCamelCase, CHARINDEX('(', @NameCamelCase) + 1, 1)) + SUBSTRING(@NameCamelCase, CHARINDEX('(', @NameCamelCase) + 2, 50)
ELSE
@NameCamelCase
END
SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' sr', ' Sr')
SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' jr', ' Jr')
SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' ii', ' II')
SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' iii', ' III')
SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' DE ', ' de ')
SELECT @NameCamelCase = REPLACE(@NameCamelCase, 'macdonald', 'MacDonald')
if (@NameCamelCase LIKE '% iv')
SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' iv', ' IV')
if ((@NameCamelCase = 'i') or (@NameCamelCase = 'ii') or (@NameCamelCase = 'iii') or (@NameCamelCase = 'iv'))
SELECT @NameCamelCase = UPPER(@NameCamelCase)
-- Return the result of the function
RETURN ISNULL(@NameCamelCase, '')
END
RETURN ISNULL(@NameCamelCase, '')
END
回答by jcomet
With SQL 2017 the function could look like this:
使用 SQL 2017，该函数可能如下所示：
create function dbo.cap_words (@str varchar(max))
returns varchar(max)
as
begin
declare @result varchar(max);
select @result = string_agg( upper(left(value,1)) + substring(value,2,999),' ') from string_split(lower(@str),' ')
return @result;
end
回答by Sagar Tippe
Here is simple thing, don't make it complicated.
这是简单的事情，不要让它复杂化。
Oracle: SELECT initcap(lower('This is MY striNg to conVerT')) FROM dual;
Oracle: SELECT initcap(lower('This is MY STRING to conVerT')) FROM dual;
