Capitalize apenas a primeira letra de cada palavra de cada frase no SQL Server

declare @a varchar(30); 

set @a = 'qWeRtY kEyBoArD TEST<>&''"X';

select stuff((
       select ' '+upper(left(T3.V, 1))+lower(stuff(T3.V, 1, 1, ''))
       from (select cast(replace((select @a as '*' for xml path('')), ' ', '<X/>') as xml).query('.')) as T1(X)
         cross apply T1.X.nodes('text()') as T2(X)
         cross apply (select T2.X.value('.', 'varchar(30)')) as T3(V)
       for xml path(''), type
       ).value('text()[1]', 'varchar(30)'), 1, 1, '') as [Capitalize first letter only];

Isso primeiro converte a string em XML substituindo todos os espaços pela tag vazia <X/>. Em seguida, ele fragmenta o XML para obter uma palavra por linha usando nodes(). Para obter as linhas de volta a um valor, ele usa o for xml pathtruque.

No SQL Server 2016 você pode fazer isso com R, por exemplo

-- R capitalisation code stolen from here:
-- http://stackoverflow.com/questions/6364783/capitalize-the-first-letter-of-both-words-in-a-two-word-string

EXEC sp_execute_external_script
    @language = N'R',
    @script = N'
simpleCap <- function(x) {
  s <- strsplit(x, " ")[[1]]
  paste(toupper(substring(s, 1,1)), substring(s, 2),
        sep="", collapse=" ")
}             

OutputDataSet <- as.data.frame((sapply(as.vector(InputDataSet$xtext), simpleCap)))',
    @input_data_1 = N'SELECT LOWER(testString) xtext FROM dbo.testStrings'
WITH RESULT SETS ( ( properCase VARCHAR(50) NOT NULL ) );

Se você deve ou não é uma questão diferente :)

Talvez eu esteja sendo bobo, mas verificando a consulta abaixo que escrevi em relação a alguns dos fornecidos, isso parece ser um pouco mais eficiente (dependendo da indexação).

O código é um pouco estúpido, mas não existe um ditado que se parece estúpido, mas funciona, então não é estúpido.

Begin

    Declare @text Varchar(30);

    Set @text = 'qWeRtY kEyBoArD TEST<>&''"X';

    Declare @1 Varchar(2)= ' a'
      , @2 Varchar(2)= ' b'
      , @3 Varchar(2)= ' c'
      , @4 Varchar(2)= ' d'
      , @5 Varchar(2)= ' e'
      , @6 Varchar(2)= ' f'
      , @7 Varchar(2)= ' g'
      , @8 Varchar(2)= ' h'
      , @9 Varchar(2)= ' i'
      , @10 Varchar(2)= ' j'
      , @11 Varchar(2)= ' k'
      , @12 Varchar(2)= ' l'
      , @13 Varchar(2)= ' m'
      , @14 Varchar(2)= ' n'
      , @15 Varchar(2)= ' o'
      , @16 Varchar(2)= ' p'
      , @17 Varchar(2)= ' q'
      , @18 Varchar(2)= ' r'
      , @19 Varchar(2)= ' s'
      , @20 Varchar(2)= ' t'
      , @21 Varchar(2)= ' u'
      , @22 Varchar(2)= ' v'
      , @23 Varchar(2)= ' w'
      , @24 Varchar(2)= ' x'
      , @25 Varchar(2)= ' y'
      , @26 Varchar(2)= ' z';

Set @text=' '+@text

    Select  LTrim(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(Lower(@text) ,
                                                              @1 , Upper(@1)) ,
                                                              @2 , Upper(@2)) ,
                                                              @3 , Upper(@3)) ,
                                                              @4 , Upper(@4)) ,
                                                              @5 , Upper(@5)) ,
                                                              @6 , Upper(@6)) ,
                                                              @7 , Upper(@7)) ,
                                                              @8 , Upper(@8)) ,
                                                              @9 , Upper(@9)) ,
                                                              @10 , Upper(@10)) ,
                                                              @11 , Upper(@11)) ,
                                                              @12 , Upper(@12)) ,
                                                              @13 , Upper(@13)) ,
                                                              @14 , Upper(@14)) ,
                                                              @15 , Upper(@15)) ,
                                                              @16 , Upper(@16)) ,
                                                              @17 , Upper(@17)) ,
                                                              @18 , Upper(@18)) ,
                                                              @19 , Upper(@19)) ,
                                                              @20 , Upper(@20)) ,
                                                            @21 , Upper(@21)) ,
                                                    @22 , Upper(@22)) , @23 ,
                                            Upper(@23)) , @24 , Upper(@24)) ,
                            @25 , Upper(@25)) , @26 , Upper(@26)));


end

Outra opção é lidar com isso via SQLCLR. Existe até um método já disponível no .NET que faz isso: TextInfo.ToTitleCase (em System.Globalization). Este método colocará em maiúsculas a primeira letra de cada palavra e em minúsculas as letras restantes. Ao contrário das outras propostas aqui, também pula palavras que estão todas em maiúsculas, assumindo que sejam siglas. Obviamente, se esse comportamento for desejado, seria bastante fácil atualizar qualquer uma das sugestões do T-SQL para fazer isso também.

Um benefício do método .NET é que ele pode usar letras maiúsculas que são caracteres suplementares. Por exemplo: DESERET SMALL LETTER OW tem um mapeamento maiúsculo de DESERET CAPITAL LETTER OW ^{(ambos aparecem como caixas quando coloco aqui)} , mas a UPPER()função não altera a versão minúscula para maiúscula, mesmo quando o Collation padrão para o banco de dados atual é definido como Latin1_General_100_CI_AS_SC. Isso parece consistente com a documentação do MSDN que não lista UPPERe LOWERno gráfico de funções que se comportam de maneira diferente ao usar um _SCCollation: Collation and Unicode Support: Supplementary Characters .

SELECT N'DESERET SMALL LETTER OW' AS [Label], NCHAR(0xD801)+NCHAR(0xDC35) AS [Thing]
UNION ALL
SELECT N'DESERET CAPITAL LETTER OW' AS [Label], NCHAR(0xD801)+NCHAR(0xDC0D) AS [Thing]
UNION ALL
SELECT N'SmallButShouldBeCapital' AS [Label], UPPER(NCHAR(0xD801)+NCHAR(0xDC35)) AS [Thing]

Retorna (ampliado para que você possa realmente ver o caractere suplementar):

Você pode ver a lista completa (e atual) de caracteres que são minúsculos e mudar para maiúsculas usando o seguinte recurso de pesquisa em Unicode.org (você pode ver os caracteres suplementares rolando para baixo até chegar ao "DESERET" seção, ou apenas clique Control-Fe procure por essa palavra):

http://unicode.org/cldr/utility/list-unicodeset.jsp?a=%5B%3AChanges_When_Titlecased%3DYes%3A%5D

Embora, para ser honesto, isso não seja um grande benefício, pois é duvidoso que alguém esteja realmente usando qualquer um dos personagens suplementares que podem ser incluídos no título. De qualquer forma, aqui está o código SQLCLR:

using System.Data.SqlTypes;
using System.Globalization;
using Microsoft.SqlServer.Server;

public class TitleCasing
{
    [return: SqlFacet(MaxSize = 4000)]
    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static SqlString TitleCase([SqlFacet(MaxSize = 4000)] SqlString InputString)
    {
        TextInfo _TxtInf = new CultureInfo(InputString.LCID).TextInfo;
        return new SqlString (_TxtInf.ToTitleCase(InputString.Value));
    }
}

Aqui está a sugestão de @MikaelEriksson - ligeiramente modificada para lidar com NVARCHARdados, bem como pular palavras que são todas em maiúsculas (para corresponder mais de perto ao comportamento do método .NET) - junto com um teste dessa implementação T-SQL e de a implementação SQLCLR:

SET NOCOUNT ON;
DECLARE @a NVARCHAR(50);

SET @a = N'qWeRtY kEyBoArD TEST<>&''"X one&TWO '
         + NCHAR(0xD801)+NCHAR(0xDC28)
         + N'pPLe '
         + NCHAR(0x24D0) -- ⓐ  Circled "a"
         + NCHAR(0xFF24) -- Ｄ  Full-width "D"
         + N'D u'
         + NCHAR(0x0308) -- ̈  (combining diaeresis / umlaut)
         + N'vU'
         + NCHAR(0x0308) -- ̈  (combining diaeresis / umlaut)
         + N'lA';
SELECT @a AS [Original];

SELECT STUFF((
       SELECT N' '
              + IIF(UPPER(T3.V) <> T3.V COLLATE Latin1_General_100_BIN2, 
                    UPPER(LEFT(T3.V COLLATE Latin1_General_100_CI_AS_SC, 1))
                    + LOWER(STUFF(T3.V COLLATE Latin1_General_100_CI_AS_SC, 1, 1, N'')),
                    T3.V)
       FROM (SELECT CAST(REPLACE((SELECT @a AS N'*' FOR XML PATH('')), N' ', N'<X/>')
                    AS XML).query('.')) AS T1(X)
       CROSS APPLY T1.X.nodes('text()') AS T2(X)
       CROSS APPLY (SELECT T2.X.value('.', 'NVARCHAR(70)')) AS T3(V)
       FOR XML PATH(''), TYPE
       ).value('text()[1]', 'NVARCHAR(70)') COLLATE Latin1_General_100_CI_AS_SC, 1, 1, N'')
                AS [Capitalize first letter only];

SELECT dbo.TitleCase(@a) AS [ToTitleCase];

Outra diferença de comportamento é que essa implementação T-SQL específica se divide apenas em espaços, enquanto o ToTitleCase()método considera a maioria das não letras como separadores de palavras (daí a diferença no tratamento da parte "um&DOIS").

Ambas as implementações tratam de sequências de combinação corretamente. Cada uma das letras acentuadas em "üvÜlA" é composta por uma letra base e uma combinação de trema / trema (os dois pontos acima de cada letra), e são convertidas corretamente para o outro caso em ambos os testes.

Finalmente, uma desvantagem inesperada para a versão SQLCLR é que, ao fazer vários testes, encontrei um bug no código .NET relacionado ao manuseio das letras circuladas (que agora foi relatado no Microsoft Connect — ATUALIZAÇÃO: o Connect foi movido para /dev/null-- literalmente -- então talvez eu precise reenviar isso se o problema ainda existir). A biblioteca .NET trata as letras circuladas como separadores de palavras, razão pela qual não transforma o "ⓐＤD" em "Ⓐｄd" como deveria.

Para sua informação

Uma função SQLCLR pré-configurada que encapsula o TextInfo.ToTitleCasemétodo mencionado acima agora está disponível na versão gratuita do SQL# (que eu escrevi) como String_ToTitleCase e String_ToTitleCase4k .

?

Como alternativa à resposta de Mikael Eriksson , você pode considerar o uso do tratamento T-SQL proprietário da configuração de variáveis ​​em instruções de seleção de várias linhas.

No SQL Server, quando uma variável está sendo definida como parte de uma instrução SELECT, cada linha executará uma iteração da lógica definida.

As pessoas costumam usar esse método para concatenar strings, embora não seja compatível e haja alguns problemas oficialmente documentados com ele . O problema oficial está relacionado a características particulares de ORDER BY, e não precisamos disso aqui, então talvez seja uma opção segura.

Aqui, iteramos as 26 letras do alfabeto e as substituímos por uma versão em maiúsculas se forem precedidas por um espaço. (Preparamos a string inicialmente colocando a primeira letra em maiúscula e deixando o resto em minúscula, como você fez na sua pergunta.)

O SQL é um pouco complexo porque requer o uso de uma Tabela Tally - uma tabela de números - para gerar as 26 iterações de substituição que está fazendo. Você pode criar uma função definida pelo usuário (TVF) com valor de tabela em linha para produzir essa tabela de números ou pode até usar uma tabela física.

Uma desvantagem dessa opção é que ela não pode fazer parte de um TVF embutido, pois precisa envolver a configuração de uma variável. Portanto, se você quiser aplicar esse método a uma coluna de sua saída, precisará envolvê-lo em um TVF de várias instruções ou em uma função escalar definida pelo usuário.

No entanto, seu plano de consulta é muito mais simples e provavelmente é significativamente mais rápido que o método XML. Você pode argumentar que é mais fácil de entender também (especialmente se você tiver sua própria tabela de contagem).

DECLARE
    @a VARCHAR(15) = 'qWeRtY kEyBoArD';

SELECT
    @a = UPPER(LEFT(@a,1)) + LOWER(SUBSTRING(@a,2,LEN(@a)));

WITH TallyTableBase AS
(
    SELECT
        0 AS n
    FROM    (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS t(n)
)
SELECT
    @a = REPLACE(@a, ' ' + CHAR(n.n), ' ' + CHAR(n.n))
FROM        (
                SELECT      TOP 26 ROW_NUMBER() OVER (ORDER BY (SELECT 1)) + 64 AS n
                FROM        TallyTableBase a
                CROSS JOIN  TallyTableBase b
            ) AS n;

SELECT
    @a AS [NewValue];

(Eu testei isso usando uma string muito maior e foi cerca de 6ms vs 14ms para a solução XML.)

Há uma série de limitações adicionais com esta solução. Conforme escrito, ele assume uma ordenação que não diferencia maiúsculas de minúsculas, embora você possa eliminar esse problema especificando uma ordenação ou executando LCASE no termo de pesquisa, ao custo de algum desempenho. Ele também aborda apenas letras ASCII padrão e depende de seu posicionamento no conjunto de caracteres , portanto, não faria nada com ñ.

Supondo que você esteja apenas procurando capitalizar palavras após um espaço, aqui está outra maneira de fazer isso.

DECLARE @String VARCHAR(1000)
SET @String = 'qWeRtY kEyBoArD tEst'

/*
Set the string to all lower case and
add a space at the beginning to ensure
the first letter gets capitalized
in the CTE
*/
SET @String = LOWER(' ' + @String)  

/*
Use a Tally "Table" as a means of
replacing the letter after the space
with the capitalize version of the
letter
*/
;WITH TallyTable
AS
(
    SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as N
    FROM master.sys.all_columns a CROSS JOIN master.sys.all_columns b

)
SELECT @String = REPLACE(@String,SUBSTRING(@String,CHARINDEX(' ',@String,N), 2),UPPER(SUBSTRING(@String,CHARINDEX(' ',@String,N), 2)))
FROM TallyTable
WHERE CHARINDEX(' ',@String,N) <> 0

--Remove the space added to the beginning of the string earlier
SET @String = RIGHT(@String,LEN(@String) - 1)

Pode não ser à prova de balas, mas espero que seja uma contribuição útil para este tópico.

DECLARE @t VARCHAR(50) = 'the quick brown fox jumps over the lazy dog', @i INT = 0

DECLARE @chk VARCHAR(1)

WHILE @i <= LEN(@t)
BEGIN
    SELECT @chk=SUBSTRING(@t,@i,1)
        IF @chk = CHAR(32)
        BEGIN
            SET @t = STUFF(@t,@i+1,1,UPPER(SUBSTRING(@t,@i+1,1)))
        END
    SET @i=@i+1
END
PRINT @t

Abaixo está o procedimento que usei em um banco de dados Firebird para fazer isso. Provavelmente pode ser limpo muito, mas fez o trabalho para mim.

set term ~;

Create Procedure EachWordCap

As

Declare Variable lcaption varchar(33);
Declare Variable lcurrentpos integer;
Declare Variable lstringlen integer;
begin
    for select ' ' || trim(lower(imagedata.imagename)) from imagedata
    where imagedata.imagename is not null and imagedata.imagename != ''
    into :lcaption
    do 
    begin
        lcurrentpos = 0;
        lstringlen = char_length(lcaption);
        while (lcurrentpos != 1) do
        begin
            lcurrentpos = position(' ', lcaption, iif(lcurrentpos = 0, 1,lcurrentpos)) + 1 ;
            lcaption = left(lcaption,lcurrentpos - 1) || upper(substring(lcaption from lcurrentpos for 1)) || right(lcaption,lstringlen - lcurrentpos);
        end
        --Put what you want to do with the text in here
    end
end~
set term ;~

CTEs recursivos são muito bons para esse tipo de coisa.

Provavelmente não é particularmente eficiente para grandes operações, mas permite esse tipo de operação em uma instrução de seleção SQL pura:

declare @a varchar(100) 

set @a = 'tHe qUiCk bRoWn FOX jumps   OvEr The lAZy dOG';

WITH [CTE] AS (
  SELECT CAST(upper(Left(@a,1)) + lower(substring(@a,2,len(@a))) AS VARCHAR(100)) AS TEXT,
         CHARINDEX(' ',@a) AS NEXT_SPACE
  UNION ALL
  SELECT CAST(Left(TEXT,NEXT_SPACE) + upper(SubString(TEXT,NEXT_SPACE+1,1)) + SubString(TEXT,NEXT_SPACE+2,1000) AS VARCHAR(100)),
         CHARINDEX(' ',TEXT, NEXT_SPACE+1)
  FROM [CTE]
  WHERE NEXT_SPACE <> 0
)

SELECT TEXT
FROM [CTE]
WHERE NEXT_SPACE = 0

Resultado:

The Quick Brown Fox Jumps   Over The Lazy Dog

Eu gosto desta versão. É simples, e pode ser usado para criar uma função, basta ter a versão correta do SQL Server:

WITH words
AS (
    SELECT upper(left(Value, 1)) + lower(substring(Value, 2, len(Value))) AS word
    FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ')
    )
SELECT STRING_AGG(words.word, ' ')
FROM words