内置函数将每个单词的第一个字母大写

Question

如果 SQL Server 中已经存在这样的函数，我不想为此创建自定义函数。

输入字符串：这是我要转换的字符串
预期输出：这是我要转换的字符串

Answer 1

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(4000)

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 (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
        SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))

    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 

Answer 2

AFAIK，SQL Server 没有内置的功能。
您必须为其编写自定义函数。

试试这个。

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  

输出：

Input String    : 'microSoft sql server'
Output String   : 'Microsoft Sql Server'

Answer 3

我不得不说“不，那不存在”。这是基于多年来对 T-SQL 中可用字符串函数的研究以及最近在 SQL Server 2008 R2 中进行的一些为期 5 天的课程。

当然，我仍然可能是错的:)。

Answer 4

如果您的操作的目标是美化名称字符串，则正确的大写可以定义为由非字母字符分隔的每个单词的第一个字母。

其他解决方案没有考虑：

1. 保留间距（尤其是尾随空格）。
2. 保留 NULL、空字符串或仅包含空格的字符串。
3. 处理的不仅仅是空格（例如破折号、逗号、下划线等...）
4. 处理单词/标记之间的多个非字母字符。
5. 处理异常（例如 McDonald 或 III，如“James William
   Bottomtooth the III”）。

注意：我的解决方案不处理异常。
如果您非常关心这些，那么我建议为这些编写一个 CLR C# 程序集，因为这会很棘手，而字符串是 C# 擅长的领域。
这里的另一个解决方案试图解决这个问题，但它仍然需要“ivan可怕的IV”并输出“**IV***an Terrible The IV*”。

这是我想出的功能：

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

你可以这样称呼它：

SELECT dbo.fs_PascalCase(NULL)[Null],
       dbo.fs_PascalCase('')[EmptyString],
       dbo.fs_PascalCase('hello   how are-you TODAY    ')[LongString]

输出将如下所示：

Answer 5

我的策略

• 如果名称已经是大小写混合的，请相信它是正确的。
• 如果名称不是混合大小写，则执行以下操作：
• 修剪名称以消除空格
• 考虑以“Mc”开头的名称，例如“McDavid”
• 考虑带撇号的名称，例如 O'Reilly
• 考虑连字符名称 (已婚姓名）“Anderson-Johnson”
• 考虑多个单词名称，例如“La Russa”
• 确保名称字段中包含的后缀适当大写

代码

这是我对此的原始帖子：在 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

Answer 6

对于 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

Answer 7

像我一样，很多人可能正在寻找查询中的解决方案，查询创建功能，我想出了一种不同的方法：

SELECT REPLACE(
    STUFF( 
        (SELECT' '+ LTRIM(RTRIM(UPPER(SUBSTRING(value, 1,1))+LOWER(SUBSTRING(value, 2, LEN(value)))))
         FROM STRING_SPLIT([Message], ' ')
         FOR XML PATH('')
         ), 1, 1, ''
   ), ''/*Control delimiters here*/, '') FROM [dbo].[MessageQueue]

将 [MessageQueue] 表更改为您自己的表，并将 [Message] 更改为您的字段。

函数 STRING_SPLIT 可能需要将 SQL 兼容性级别增加到 130。

使用外部 REPLACE 函数设置所需的任何分隔符。

Answer 8

我采用了 @ashish.chotalia 的答案，并将其转换为带有输出参数的临时过程。如果不允许您在生产数据库中创建函数，但您确实需要此功能，这对某人来说可能很方便。

IF OBJECT_ID('tempdb..#InitCap') IS NOT NULL 
  DROP PROCEDURE #InitCap
GO
CREATE PROCEDURE #InitCap ( @InputString varchar(4000), @OutputString varchar(4000) OUTPUT) 
AS
BEGIN
  DECLARE @Index          INT;
  DECLARE @Char           CHAR(1);
  DECLARE @PrevChar       CHAR(1);

  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
      SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char));
    END;

    SET @Index = @Index + 1;
  END
END
GO

DECLARE @Name NVARCHAR(4000)= 'my string to convert. test/test something:else';
EXEC #InitCap @Name, @OutputString = @Name OUTPUT;
SELECT @Name;

Answer 9

您可以使用 string_split 和 string_agg 函数来获得所需的输出。请参阅此示例。但是，此方法也会将字符串中的多个空格替换为单个空格。

declare @String nvarchar(max) = 'This   is my string to convert';

select (select string_agg(Stuff(value,1,1,upper(left(value,1))),' ') from string_split(@String,' '))

print @String

-- Output: "This Is My String To Convert"

Answer 10

这是很简单的事情，不要把它搞复杂。

甲骨文：
SELECT initcap(lower('这是我要转换的字符串')) FROM Dual;