O título deste artigo não está errado, pois foi proposital a grafia “pPROPERcASE”. Algo solicitado com frequência é como retirar acentuação de textos, sendo que como formatar corretamente um nome próprio (seja de pessoa, de local, etc.) também é recorrente.
1. Introdução
Às vezes me deparo com alguém solicitando um código sql que torne maiúscula a primeira letra de cada palavra em uma frase. Isto geralmente se aplica a nomes próprios (pessoas, logradouros, cidades, etc.) bem como a títulos de artigos (tem gente que prefere assim), além de outras aplicações. E esse tipo de solicitação ocorreu recentemente, em um grupo de sql no whatsapp. A primeira ideia que me veio à mente foi separar a frase em palavras (tokens), tornar maiúscula a primeira letra do token; e, ao final, reagrupar as palavras na frase original, na mesma ordem. Ou seja, dentro do princípio trabalhar com conjunto de linhas (dataset) e não linha a linha.
Antes de prosseguir, uma observação. Desenvolver códigos T-SQL exige tempo e dedicação para estudos e testes, afora conhecimento técnico. Sempre que utilizo código T-SQL de outra pessoa como base eu informo o nome do autor e/ou o endereço web da página onde foi publicado, de modo a respeitar o esforço de programação de quem o desenvolveu. Do contrário tem-se o plágio, que além de ser uma violação dos direitos autorais é também uma demonstração de falta de ética profissional. Nesse ponto faço minhas as palavras de Edvaldo Castro e de Erickson Ricci presentes no artigo “PLÁGIO – Sério mesmo?”.
2. Implementações
O objetivo deste artigo é construir solução que trabalhe com conjunto de linhas, no lugar do habitual – e ineficiente – tratamento linha a linha.
-- código #2.1
-- © José Diz / Porto SQL
CREATE TABLE Endereço (
Id integer identity not null,
Tipo varchar(10) not null
check (Tipo in ('rua', 'praça', 'avenida', 'alameda')),
Logradouro varchar(120) not null,
Número varchar(12) not null,
constraint I1_Endereço primary key (Id)
);
É uma tabela bem simples, somente para demonstração dos códigos sql propostos no artigo.
Para a carga da tabela acima temos o seguinte código sql:
-- código #2.2
-- © José Diz / Porto SQL
INSERT into Endereço (Tipo, Logradouro, Número) values
('rua', 'joão visconde de cairu', '1251'),
('praça', 'PROF. ALBERTO MAZZONI', 's/n'),
('alameda', 'santa bárbara d''oeste', '18'),
('rua', 'VAI E VOLTA', '33');
2.1. string_split > string_agg
Para implementar em t-sql o roteiro proposto na introdução vamos utilizar as funções string_split, upper, stuff e string_agg.
Função string_split. Conforme documentação, esta função divide uma cadeia de caracteres em linhas de subcadeias de caracteres com base em um caractere separador especificado.
Ela possui 3 parâmetros:
STRING_SPLIT ( string , separator [ , enable_ordinal ] )
onde:
string: texto a ser desmembrado em partes;
separator: caractere que define qual é o separador que servirá como base para a separação do texto em partes;
enable_ordinal: parâmetro opcional, informa se no resultado constará coluna indicando a posição de cada parte do texto original.
-- código #2.3
-- © José Diz / Porto SQL
SELECT E.Id, E.Logradouro,
lower (S.value) as Token, S.ordinal as Pos
from Endereço as E
cross apply string_split (E.Logradouro, ' ', 1) as S;
tendo o seguinte resultado
Função upper. Retorna um texto todo em maiúsculas.
UPPER ( character_expression )
O conteúdo de character_expression é transformado em maiúsculas. Não precisa explicar mais nada, certo?
Função stuff. Esta função substitui trecho de um texto por outro texto.
STUFF ( character_expression, start, length, replace_with_expression )
onde:
character_expression: é o texto originado que será alterado;
start: a partir de qual posição, no texto original, ocorrerá a modificação;
length: a partir da posição inicial, quantos caracteres serão retirados do texto original;
replace_with_expression: texto que será incluído no texto original, a partir da posição start.
Acrescentando as funções stuff() e upper() no código sql anterior, o código sql fica assim:
-- código #2.4
-- © José Diz / Porto SQL
with
-- getToken: separa a frase em tokens, já em minúscula
gToken as (
SELECT E.Id, E.Logradouro,
lower (S.value) as Token, S.ordinal as Pos
from Endereço as E
cross apply string_split (E.Logradouro, ' ', 1) as S
)
SELECT Id, Logradouro, Token, Pos,
stuff (Token, 1, 1, upper (left (Token, 1))) as TokenProper
from gToken;
e obtemos o seguinte resultado intermediário:
Observe que na coluna TokenProper está o token já pronto, com a primeira letra em maiúscula e restante em minúsculas.
Função string_agg. Esta função concatena diversos elementos em um único, intercalando-os com um separador pré-definido. Ela é o inverso da função string_split.
Esta função possui 2 parâmetros e, opcionalmente, uma cláusula em que se define a ordem em que os elementos serão concatenados:
STRING_AGG ( expression, separator ) [ <order_clause> ]
<order_clause> ::=
WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )
onde:
expression: contém uma expressão dos elementos a serem concatenados;
separator: define o que será inserido entre cada elemento concatenado.
E agora vamos ao código sql completo, com a montagem do texto final:
-- código #2.5
-- © José Diz / Porto SQL
with
-- getToken: separa a frase em tokens, já em minúscula
gToken as (
SELECT E.Id, E.Logradouro,
lower (S.value) as Token, S.ordinal as Pos
from Endereço as E
cross apply string_split (E.Logradouro, ' ', 1) as S
),
-- setProper: torna maiúscula a primeira letra do token
sProper as (
SELECT Id, Logradouro, Token, Pos,
stuff (Token, 1, 1, upper (left (Token, 1))) as TokenProper
from gToken
)
-- exibe resultado, com a frase reagrupada
SELECT Id, Logradouro,
string_agg (TokenProper, ' ') within group (order by Pos) as [Logradouro ok]
from sProper
group by Id, Logradouro;
E ao executar o código #2.5 temos o seguinte resultado:
No código #2.5, a CTE gToken separa cada palavra da frase, gerando os tokens já em minúscula; para isto utiliza a função string_split() e configurando o terceiro parâmetro – enable_ordinal – para 1. Ou seja, junto a cada token deve ser informada a posição do token na frase.
A segunda CTE, sProper, torna maiúscula a primeira letra do token. Isto é possível com o uso conjunto da função upper() – para tornar maiúscula – e da função stuff() – para substituir o primeiro caractere do token.
Simples, direto e provavelmente eficiente.
Mas precisamos acrescentar algumas melhoras; por exemplo, não capitalizar quando o token for um conectivo como “de”, “da” ou do”. E também tratar casos de elisão: “d’Água”, por exemplo. Para esses casos basta modificar a CTE sProper, passando então a termos o seguinte código sql:
-- código #2.6
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com
with
-- getToken: separa a frase em tokens, já em minúscula
gToken as (
SELECT E.Id, E.Logradouro,
lower (S.value) as Token, S.ordinal as Pos
from Endereço as E
cross apply string_split (E.Logradouro, ' ', 1) as S
),
-- setProper: torna maiúscula a primeira letra do token
sProper as (
SELECT Id, Logradouro, Token, Pos,
case when (len (Token) = 1) then Token
when (Token in ('de', 'da', 'do', 'das', 'dos')) then Token
when left (Token, 2) = 'd''' then stuff (Token, 3, 1, upper (substring
(Token, 3, 1)))
else stuff (Token, 1, 1, upper (left (Token, 1)))
end as TokenProper
from gToken
)
-- exibe resultado, com a frase reagrupada
SELECT Id, Logradouro,
string_agg (TokenProper, ' ') within group (order by Pos) as [Logradouro ok]
from sProper
group by Id, Logradouro;
O resultado da execução do código sql anterior é o seguinte:
Agora sim temos um resultado coerente.
2.2. DelimitedSplit8K > Blackbox XML
Sobre a solução do item anterior, uma restrição é que a função string_split somente foi implementada no sql server 2016 e a função string_agg na versão 2017 do sql server. Restringindo ainda um pouco mais, na função string_split o parâmetro enable_ordinal somente pode ser utilizado a partir da versão 2022; e o mesmo se aplica à <order clause> da função string_agg. Em resumo, a ideia proposta naquele item somente é possível de se implementar caso se utilize a versão 2022 (ou mais recente) do SQL Server; ou então o Azure SQL.
O que então deve ser alterado no código #2.6 para que ele funcione também em versões do SQL Server anteriores a 2022? A solução encontra-se em 2 artigos do Porto SQL:
-
- Separar em partes uma lista de valores: em que é explicado o funcionamento da função de usuário DelimitedSplit8K;
- Concatenação de várias linhas de uma mesma coluna: em que, dentre alguns métodos consta o Blackbox XML.
DelimitedSplit8K. Essa função de usuário foi escrita por Jeff Moden e é eficiente, além de ser uma função de usuário do tipo inline table-valued. O código T-SQL da função está ao final deste artigo e a explicação detalhada sobre o funcionamento encontra-se no artigo “An Improved SQL 8K CSV Splitter Function”.
Ela possui 2 parâmetros: (1) string e (2) separator. O primeiro parâmetro contém o texto multivalorado a ser separado; é uma expressão de texto (char, varchar etc). O segundo parâmetro contém o delimitador que separa cada valor do texto; é uma expressão de caractere único.
O retorno da função é uma tabela de duas colunas: ItemNumber, contendo a posição em que o valor ocorre no texto multivalorado; e Item, que contém o valor.
A explicação do funcionamento dessa função de usuário, bem como exemplos, estão no item 2.2 do artigo Separar em partes uma lista de valores. O código fonte simplificado da função também está disponível lá.
Blackbox XML. Não é por acaso que esse método se chama blackbox: todo mundo usa mas poucos entendem como funciona. Ou seja, uma caixa-preta. A seguir passo a passo da criação do método, o que facilitará entender como ele funciona. Em resumo, ao utilizar a cláusula FOR XML, obtém-se a concatenação mas com o resultado sendo do tipo de dados XML. É quando entra em ação a funçãoSTUFF(), utilizada como invólucro e permitindo inclusive retirar o delimitador ao final.
Toda a explicação do funcionamento desse método, bem como exemplos, estão no item 2.2 do artigo Concatenação de várias linhas de uma mesma coluna.
Adaptação do código sql. A partir do código #2.6, e com a substituição da função string_split pela função de usuário DelimitedSplit8K bem como a substituição da função string_agg pelo método BlackBox XML, temos então o seguinte código sql:
-- código #2.7
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com
with
-- getToken: separa a frase em tokens, já em minúscula
gToken as (
SELECT E.Id, E.Logradouro,
lower (S.Item) as Token, S.ItemNumber as Pos
from Endereço as E
cross apply dbo.DelimitedSplit8K (E.Logradouro, ' ') as S
),
-- setProper: torna maiúscula a primeira letra do token
sProper as (
SELECT Id, Logradouro, Token, Pos,
case when (len (Token) = 1) then Token
when (Token in ('de', 'da', 'do', 'das', 'dos')) then Token
when left (Token, 2) = 'd''' then stuff (Token, 3, 1, upper (substring (Token, 3, 1)))
else stuff (Token, 1, 1, upper (left (Token, 1))) end as TokenProper
from gToken
)
-- exibe resultado, com a frase reagrupada
SELECT Id, Logradouro,
stuff ((SELECT ' ' + T.TokenProper
from sProper as T
where T.Id = I.Id
order by T.Pos
for xml path('')),
1, 1, '') as [Logradouro ok]
from sProper as I
group by Id, Logradouro;
Em negrito o que foi modificado. Ou seja, temos agora uma implementação que funciona a partir da versão SQL Server 2008 (inclusive). Observe como foi fácil converter o código #2.6 no código #2.7, graças à programação modular com uso de CTE.
Os códigos acima foram somente para demonstração e por isso não será avaliada a etapa de otimização, pois o objetivo final é montar funções de usuário para serem utilizadas.
3. Transformando em função de usuário
Agora que já temos demonstração de implementações utilizando diferentes conjuntos de funções, podemos passar para o próximo passo que é transformar o código sql em uma função de usuário, de modo que possa ser utilizado de maneira simples.
O objetivo é criar função de usuário do tipo inline table-valued (iTVF) que, a princípio, possui desempenho melhor do que as funções escalares. Ela retorna como resultado uma tabela e não somente um valor, como a função de usuário escalar.
A primeira modificação é retirar as referências à tabela Endereço, pois o texto a ser ajustado é recebido como parâmetro.
3.1. string_split > string_agg
Essa transformação terá como base o código #2.6.
-- código #3.1
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com
CREATE or alter FUNCTION dbo.pROPERcASE_2022 (
@Texto varchar(8000)
)
returns table
return
with
-- getToken: separa a frase em tokens, já em minúscula
gToken as (
SELECT S.ordinal as Pos,
lower (S.value) as Token
from string_split (@Texto, ' ', 1) as S
),
sProper as (
-- setProper: torna maiúscula a primeira letra do token
SELECT Pos,
case when (len (Token) = 1) then Token
when (Token in ('de', 'da', 'do', 'das', 'dos')) then Token
when left (Token, 2) = 'd''' then stuff (Token, 3, 1, upper (substring (Token, 3, 1)))
else stuff (Token, 1, 1, upper (left (Token, 1))) end as TokenProper
from gToken
)
-- monta resultado, com a frase reagrupada
SELECT string_agg (TokenProper, ' ') within group (order by Pos) as Capitalizado
from sProper;
go
A utilização é bem simples. Por ser uma função de usuário do tipo inline table-valued, ela é acionada na cláusula FROM:
-- código #3.2
-- © José Diz / Porto SQL
SELECT E.Id,
concat (T.Capitalizado, ' ', L.Capitalizado, ', ', E.Número) as Endereço
from Endereço as E
cross apply dbo.pRPOPERcASE_2022 (E.Tipo) as T
cross apply dbo.pROPERcASE_2022 (E.Logradouro) as L;
O resultado é o seguinte:
3.2. DelimitedSplit8K > Blackbox XML
A conversão do código #2.7 para uma função de usuário do tipo inline table-valued segue o mesmo roteiro do item anterior. É um processo bem simples.
-- código #3.3
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com
CREATE or alter FUNCTION dbo.pROPERcASE_2008 (
@Texto varchar(8000)
)
returns table
return
with
-- getToken: separa a frase em tokens, já em minúscula
gToken as (
SELECT lower (S.Item) as Token, S.ItemNumber as Pos
from dbo.DelimitedSplit8K (@Texto, ' ') as S
),
-- setProper: torna maiúscula a primeira letra do token
sProper as (
SELECT Pos,
case when (len (Token) = 1) then Token
when (Token in ('de', 'da', 'do', 'das', 'dos')) then Token
when left (Token, 2) = 'd''' then stuff (Token, 3, 1, upper (substring (Token, 3, 1)))
else stuff (Token, 1, 1, upper (left (Token, 1)))
end as TokenProper
from gToken
)
-- monta resultado, com a frase reagrupada
SELECT stuff ((SELECT ' ' + T.TokenProper
from sProper as T
order by T.Pos
for xml path('')),
1, 1, '') as Capitalizado;
go
4. Observações
Como mencionado no início, o objetivo do artigo foi construir função de usuário do tipo inline table-valued. Pesquisando na web encontrei algumas soluções de função ProperCase, mas do tipo escalar; aquelas que processam uma linha de cada vez.
InitCap. Ao pesquisar sobre funções semelhantes, deparei com a existência da função InitCap no Oracle Database. Nesta função, além do caractere espaço como delimitador, também são considerados como separadores os caracteres especiais, como &, #, $ etc.
Outro ponto observado é que espaços múltiplos são preservados na InitCap. Entretanto, nas implementações deste artigo foram ignorados casos em que exista mais de um espaço entre palavras, sendo que no resultado sempre haverá um único espaço entre cada palavra.
Provavelmente há como implementar a função InitCap no SQL Server, mas talvez às custas de redução no desempenho, comparando com as implementações apresentadas neste artigo.
5. Material de estudo
Neste capítulo estão referências para a documentação das principais funções utilizadas no artigo bem como outros artigos referenciados, ao longo do texto.
5.1. Documentação
5.2. Artigos
An Improved SQL 8K CSV Splitter Function
Concatenação de várias linhas de uma mesma coluna
Separar em partes uma lista de valores
Porto SQL 