在 SQL Server 中将字符串中每个单词的第一个字母大写的最佳方法是什么

What’s the best way to capitalise the first letter of each word in a string in SQL Server

提问by Magpie

What's the best way to capitalise the first letter of each word in a string in SQL Server.

在 SQL Server 中将字符串中每个单词的第一个字母大写的最佳方法是什么。

回答by Espo

From http://www.sql-server-helper.com/functions/initcap.aspx

来自http://www.sql-server-helper.com/functions/initcap.aspx

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
GO

There is a simpler/smaller one here (but doesn't work if any row doesn't have spaces, "Invalid length parameter passed to the RIGHT function."):

这里有一个更简单/更小的一个（但如果任何行没有空格则不起作用，“传递给 RIGHT 函数的长度参数无效。”）：

http://www.devx.com/tips/Tip/17608

http://www.devx.com/tips/Tip/17608

回答by Shashank Gupta

If you are looking for the answer to the same question in Oracle/PLSQL then you may use the function INITCAP. Below is an example for the attribute dnamefrom a table departmentwhich has the values ('sales', 'management', 'production', 'development').

如果您正在 Oracle/PLSQL 中寻找同一问题的答案，那么您可以使用 INITCAP 函数。下面是来自表部门的属性dname的示例，该表具有值（'sales'、'management'、'production'、'development'）。

SQL> select INITCAP(dname) from department;

INITCAP(DNAME)
--------------------------------------------------
Sales
Management
Production
Development

回答by Josef

A variation of the one I've been using for quite some time is:

我已经使用了很长时间的一个变体是：

CREATE FUNCTION [widget].[properCase](@string varchar(8000)) RETURNS varchar(8000) AS
BEGIN   
    SET @string = LOWER(@string)
    DECLARE @i INT
    SET @i = ASCII('a')
    WHILE @i <= ASCII('z')
    BEGIN
        SET @string = REPLACE( @string, ' ' + CHAR(@i), ' ' + CHAR(@i-32))
        SET @i = @i + 1
    END
    SET @string = CHAR(ASCII(LEFT(@string, 1))-32) + RIGHT(@string, LEN(@string)-1)
    RETURN @string
END

You can easily modify to handle characters after items other than spaces if you wanted to.

如果需要，您可以轻松修改以处理除空格以外的项目之后的字符。

回答by Andrey Morozov

Another solution without using the loop - pure set-based approach with recursive CTE

另一种不使用循环的解决方案 - 具有递归 CTE 的纯基于集合的方法

create function [dbo].InitCap (@value varchar(max))
returns varchar(max) as
begin

    declare
        @separator char(1) = ' ',
        @result varchar(max) = '';

    with r as (
        select value, cast(null as varchar(max)) [x], cast('' as varchar(max)) [char], 0 [no] from (select rtrim(cast(@value as varchar(max))) [value]) as j
        union all
        select right(value, len(value)-case charindex(@separator, value) when 0 then len(value) else charindex(@separator, value) end) [value]
        , left(r.[value], case charindex(@separator, r.value) when 0 then len(r.value) else abs(charindex(@separator, r.[value])-1) end ) [x]
        , left(r.[value], 1)
        , [no] + 1 [no]
        from r where value > '')

    select @result = @result +
    case
        when ascii([char]) between 97 and 122 
            then stuff(x, 1, 1, char(ascii([char])-32))
        else x
    end + @separator
    from r where x is not null;

    set @result = rtrim(@result);

    return @result;
end

回答by Amrik

Here is the simplest one-line code.

这是最简单的一行代码。

select 
        LEFT(column, 1)+ lower(RIGHT(column, len(column)-1) )
     from [tablename]

回答by Kristofer

As a table-valued function:

作为表值函数：

CREATE FUNCTION dbo.InitCap(@v AS VARCHAR(MAX))
RETURNS TABLE
AS
RETURN 
WITH a AS (
    SELECT (
        SELECT UPPER(LEFT(value, 1)) + LOWER(SUBSTRING(value, 2, LEN(value))) AS 'data()'
        FROM string_split(@v, ' ')
        FOR XML PATH (''), TYPE) ret)

SELECT CAST(a.ret AS varchar(MAX)) ret from a
GO

Note that string_splitrequires COMPATIBILITY_LEVEL130.

请注意，string_split需要COMPATIBILITY_LEVEL130。

回答by Vignesh Sonaiya

BEGIN
DECLARE @string varchar(100) = 'asdsadsd asdad asd'
DECLARE @ResultString varchar(200) = ''
DECLARE @index int = 1
DECLARE @flag bit = 0
DECLARE @temp varchar(2) = ''
WHILE (@Index <LEN(@string)+1)
BEGIN
    SET @temp = SUBSTRING(@string, @Index-1, 1)
    --select @temp
    IF @temp = ' ' OR @index = 1
        BEGIN
            SET @ResultString = @ResultString + UPPER(SUBSTRING(@string, @Index, 1))
        END
    ELSE
        BEGIN

            SET @ResultString = @ResultString + LOWER(SUBSTRING(@string, @Index, 1)) 
        END 

    SET @Index = @Index+ 1--increase the index
END
SELECT @ResultString

END

结尾

回答by Roman M

For English only data.

仅适用于英文数据。

Super non-efficient from view of performance but efficient from view of productivity. Use it as one-time converter:

从性能的角度来看超级不高效，但从生产力的角度来看是高效的。将其用作一次性转换器：

SELECT 
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(

UPPER(LEFT(City,1))+LOWER(SUBSTRING(City,2,LEN(City)))

,' a', ' A')
,' b', ' B')
,' c', ' C')
,' d', ' D')
,' e', ' E')
,' f', ' F')
,' g', ' G')
,' h', ' H')
,' i', ' I')
,' j', ' J')
,' k', ' K')
,' l', ' L')
,' m', ' M')
,' n', ' N')
,' o', ' O')
,' p', ' P')
,' q', ' Q')
,' r', ' R')
,' s', ' S')
,' t', ' T')
,' u', ' U')
,' v', ' V')
,' w', ' W')
,' x', ' X')
,' y', ' Y')
,' z', ' Z')


FROM [Dictionaries].[dbo].[Cities]
  WHERE Country = 'US' AND City like '% %'
  ORDER BY City

回答by Rene

I was looking for the best way to capitalize and i recreate simple sql script

我一直在寻找最好的大写方式，我重新创建了简单的 sql 脚本

how to use SELECT dbo.Capitalyze('this is a test with multiple spaces')

如何使用 SELECT dbo.Capitalyze('这是一个有多个空格的测试')

result "This Is A Test With Multiple Spaces"

结果“这是一个有多个空格的测试”

CREATE FUNCTION Capitalyze(@input varchar(100) ) returns varchar(100) as begin

CREATE FUNCTION Capitalyze(@input varchar(100) ) 返回 varchar(100) 作为开始

declare @index int=0
declare @char as varchar(1)=' '
declare @prevCharIsSpace as bit=1
declare @Result as varchar(100)=''

set @input=UPPER(LEFT(@input,1))+LOWER(SUBSTRING(@input, 2, LEN(@input)))
set @index=PATINDEX('% _%',@input)
if @index=0
    set @index=len(@input)
set @Result=substring(@input,0,@index+1)

WHILE (@index < len(@input))
BEGIN
    SET @index = @index + 1
    SET @char=substring(@input,@index,1)
    if (@prevCharIsSpace=1)
    begin
        set @char=UPPER(@char)
        if (@char=' ')
            set @char=''
    end

    if (@char=' ')
        set @prevCharIsSpace=1
    else
        set @prevCharIsSpace=0

    set @Result=@Result+@char
    --print @Result
END
--print @Result
return @Result

end

结尾

回答by Akhil Singh

fname is column name if fname value is akhil then UPPER(left(fname,1)) provide capital First letter(A) and substring function SUBSTRING(fname,2,LEN(fname)) provide(khil) concate both using + then result is (Akhil)

fname 是列名，如果 fname 值为 akhil，则 UPPER(left(fname,1)) 提供大写首字母 (A) 和子字符串函数 SUBSTRING(fname,2,LEN(fname)) provide(khil) 使用 + 然后结果连接两者是 (Akhil)

select UPPER(left(fname,1))+SUBSTRING(fname,2,LEN(fname)) as fname
FROM [dbo].[akhil]