Como converter dados para maiúsculas e minúsculas no SQL Server

O desafio que você encontrará com essas abordagens é que você perdeu informações. Explique aos usuários de negócios que eles tiraram uma foto borrada e fora de foco e, apesar do que veem na TV, não há como torná-la nítida e focada. Sempre haverá situações em que essas regras não funcionarão e, desde que todos saibam que esse é o caso, faça isso.

Esses são dados de RH, então vou supor que estamos falando sobre obter nomes em um formato de caixa de título consistente porque o mainframe os armazena como AARON BERTRAND e queremos que o novo sistema não grite com eles. Aaron é fácil (mas não é barato). Você e Hannah já identificaram o problema com o Mc/Mac, então ele capitaliza corretamente o Mc/Mac, mas há casos em que é muito agressivo com Mackey/ Maclin /Mackenzie. Mackenzie é um caso interessante - veja como a popularidade dele cresceu como um nome de bebê

Em algum momento, haverá uma pobre criança chamada Mackenzie MacKenzie porque as pessoas são seres horríveis.

Você também vai se deparar com coisas adoráveis ​​como D'Antoni, onde devemos colocar ambas as letras ao redor da marca de verificação. Exceto para d'Autremont onde você só capitaliza a letra após o apóstrofo. Deus te ajude, porém, se você enviar um e-mail para d'Illoni, pois o nome de família deles é D'illoni.

Para contribuir com o código real, a seguir está um método CLR que usamos em uma instância de 2005 para nossos propósitos. Ele geralmente usava o ToTitleCase, exceto pela lista de exceções que construímos, quando basicamente desistimos de tentar codificar as exceções mencionadas.

namespace Common.Util
{
    using System;
    using System.Collections.Generic;
    using System.Globalization;
    using System.Text;
    using System.Text.RegularExpressions;
    using System.Threading;
    
    /// <summary>
    /// A class that attempts to proper case a word, taking into
    /// consideration some outliers.
    /// </summary>
    public class ProperCase
    {
        /// <summary>
        /// Convert a string into its propercased equivalent.  General case
        /// it will capitalize the first letter of each word.  Handled special 
        /// cases include names with apostrophes (O'Shea), and Scottish/Irish
        /// surnames MacInnes, McDonalds.  Will fail for Macbeth, Macaroni, etc
        /// </summary>
        /// <param name="inputText">The data to be recased into initial caps</param>
        /// <returns>The input text resampled as proper cased</returns>
        public static string Case(string inputText)
        {
            CultureInfo cultureInfo = Thread.CurrentThread.CurrentCulture;
            TextInfo textInfo = cultureInfo.TextInfo;
            string output = null;
            int staticHack = 0;

            Regex expression = null;
            string matchPattern = string.Empty;

            // Should think about maybe matching the first non blank character
            matchPattern = @"
                (?<Apostrophe>'.\B)| # Match things like O'Shea so apostrophe plus one.  Think about white space between ' and next letter.  TODO:  Correct it's from becoming It'S, can't -> CaN'T
                \bMac(?<Mac>.) | # MacInnes, MacGyver, etc.  Will fail for Macbeth
                \bMc(?<Mc>.) # McDonalds
                ";
            expression = new Regex(matchPattern, RegexOptions.IgnorePatternWhitespace | RegexOptions.IgnoreCase);

            // Handle our funky rules            
            // Using named matches is probably overkill as the
            // same rule applies to all but for future growth, I'm
            // defining it as such.
            // Quirky behaviour---for 2005, the compiler will 
            // make this into a static method which is verboten for 
            // safe assemblies.  
            MatchEvaluator upperCase = delegate(Match match)
            {
                // Based on advice from Chris Hedgate's blog
                // I need to reference a local variable to prevent
                // this from being turned into static
                staticHack = matchPattern.Length;

                if (!string.IsNullOrEmpty(match.Groups["Apostrophe"].Value))
                {
                    return match.Groups["Apostrophe"].Value.ToUpper();
                }

                if (!string.IsNullOrEmpty(match.Groups["Mac"].Value))
                {
                    return string.Format("Mac{0}", match.Groups["Mac"].Value.ToUpper());
                }

                if (!string.IsNullOrEmpty(match.Groups["Mc"].Value))
                {
                    return string.Format("Mc{0}", match.Groups["Mc"].Value.ToUpper());
                }

                return match.Value;
            };

            MatchEvaluator evaluator = new MatchEvaluator(upperCase);

            if (inputText != null)
            {
                // Generally, title casing converts the first character 
                // of a word to uppercase and the rest of the characters 
                // to lowercase. However, a word that is entirely uppercase, 
                // such as an acronym, is not converted.
                // http://msdn.microsoft.com/en-us/library/system.globalization.textinfo.totitlecase(VS.80).aspx
                string temporary = string.Empty;
                temporary = textInfo.ToTitleCase(inputText.ToString().ToLower());
                output = expression.Replace(temporary, evaluator);
            }
            else
            {
                output = string.Empty;
            }

            return output;
        }
    }
}

Agora que tudo isso está claro, vou terminar este lindo livro de poesia de ee cummings

Percebo que você já tem uma boa solução, mas pensei em adicionar uma solução mais simples utilizando uma função Inline-Table-Valued-Function, embora uma que dependa do uso da próxima versão "vNext" do SQL Server, que inclui o STRING_AGG()e STRING_SPLIT()funções:

IF OBJECT_ID('dbo.fn_TitleCase') IS NOT NULL
DROP FUNCTION dbo.fn_TitleCase;
GO
CREATE FUNCTION dbo.fn_TitleCase
(
    @Input nvarchar(1000)
)
RETURNS TABLE
AS
RETURN
SELECT Item = STRING_AGG(splits.Word, ' ')
FROM (
    SELECT Word = UPPER(LEFT(value, 1)) + LOWER(RIGHT(value, LEN(value) - 1))
    FROM STRING_SPLIT(@Input, ' ')
    ) splits(Word);
GO

Testando a função:

SELECT *
FROM dbo.fn_TitleCase('this is a test');

Isto é um teste

SELECT *
FROM dbo.fn_TitleCase('THIS IS A TEST');

Isto é um teste

Consulte MSDN para documentação sobre STRING_AGG() e STRING_SPLIT()

Tenha em mente que a STRING_SPLIT()função não garante a devolução de itens em nenhum pedido específico. Isso pode ser mais irritante. Há um item do Microsoft Feedback solicitando que uma coluna seja adicionada à saída de STRING_SPLIT para indicar a ordem da saída. Considere votar isso aqui

Se você deseja viver no limite e deseja usar essa metodologia, ela pode ser expandida para incluir exceções. Eu construí uma função com valor de tabela inline que faz exatamente isso:

CREATE FUNCTION dbo.fn_TitleCase
(
    @Input nvarchar(1000)
    , @SepList nvarchar(1)
)
RETURNS TABLE
AS
RETURN
WITH Exceptions AS (
    SELECT v.ItemToFind
        , v.Replacement
    FROM (VALUES /* add further exceptions to the list below */
          ('mca', 'McA')
        , ('maca','MacA')
        ) v(ItemToFind, Replacement)
)
, Source AS (
    SELECT Word = UPPER(LEFT(value, 1 )) + LOWER(RIGHT(value, LEN(value) - 1))
        , Num = ROW_NUMBER() OVER (ORDER BY GETDATE())
    FROM STRING_SPLIT(@Input, @SepList) 
)
SELECT Item = STRING_AGG(splits.Word, @SepList)
FROM (
    SELECT TOP 214748367 Word
    FROM (
        SELECT Word = REPLACE(Source.Word, Exceptions.ItemToFind, Exceptions.Replacement)
            , Source.Num
        FROM Source
        CROSS APPLY Exceptions
        WHERE Source.Word LIKE Exceptions.ItemToFind + '%'
        UNION ALL
        SELECT Word = Source.Word
            , Source.Num
        FROM Source
        WHERE NOT EXISTS (
            SELECT 1
            FROM Exceptions
            WHERE Source.Word LIKE Exceptions.ItemToFind + '%'
            )
        ) w
    ORDER BY Num
    ) splits;
GO

Testando isso mostra como funciona:

SELECT *
FROM dbo.fn_TitleCase('THIS IS A TEST MCADAMS MACKENZIE MACADAMS', ' ');

Este é um teste McAdams Mackenzie MacAdams

A melhor solução que encontrei pode ser encontrada aqui .

Alterei um pouco o script: adicionei LTRIM e RTRIM ao valor retornado, pois, em alguns casos, o script estava adicionando espaços após o valor.

Exemplo de uso para visualizar a conversão de dados em MAIÚSCULAS para maiúsculas, com exceções:

SELECT <column>,[dbo].[fProperCase](<column>,'|APT|HWY|BOX|',NULL)
FROM <table> WHERE <column>=UPPER(<column>)

O aspecto realmente simples e poderoso desse script é a capacidade de definir exceções dentro da própria chamada de função.

Uma nota de cautela, no entanto:
Como escrito atualmente, o script não lida com os sobrenomes Mc[AZ]%, Mac[AZ]% etc. corretamente. Atualmente estou trabalhando em edições para lidar com esse cenário.

Como alternativa, alterei o parâmetro retornado da função: REPLACE(REPLACE(LTRIM(RTRIM((@ProperCaseText)))),'Mcd','McD'),'Mci','McI'), etc. ...

Este método obviamente requeria conhecimento prévio dos dados e não é o ideal. Tenho certeza de que há uma maneira de resolver isso, mas estou no meio de uma conversão e atualmente não tenho tempo para me dedicar a esse problema irritante.

Aqui está o código:

CREATE FUNCTION [dbo].[fProperCase](@Value varchar(8000), @Exceptions varchar(8000),@UCASEWordLength tinyint)
returns varchar(8000)
as
/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Function Purpose: To convert text to Proper Case.
Created By:             David Wiseman
Website:                http://www.wisesoft.co.uk
Created:                2005-10-03
Updated:                2006-06-22
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
INPUTS:

@Value :                This is the text to be converted to Proper Case
@Exceptions:            A list of exceptions to the default Proper Case rules. e.g. |RAM|CPU|HDD|TFT|
                              Without exception list they would display as Ram, Cpu, Hdd and Tft
                              Note the use of the Pipe "|" symbol to separate exceptions.
                              (You can change the @sep variable to something else if you prefer)
@UCASEWordLength: You can specify that words less than a certain length are automatically displayed in UPPERCASE

USAGE1:

Convert text to ProperCase, without any exceptions

select dbo.fProperCase('THIS FUNCTION WAS CREATED BY DAVID WISEMAN',null,null)
>> This Function Was Created By David Wiseman

USAGE2:

Convert text to Proper Case, with exception for WiseSoft

select dbo.fProperCase('THIS FUNCTION WAS CREATED BY DAVID WISEMAN @ WISESOFT','|WiseSoft|',null)
>> This Function Was Created By David Wiseman @ WiseSoft

USAGE3:

Convert text to Proper Case and default words less than 3 chars to UPPERCASE

select dbo.fProperCase('SIMPSON, HJ',null,3)
>> Simpson, HJ

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
begin
      declare @sep char(1) -- Seperator character for exceptions
      declare @i int -- counter
      declare @ProperCaseText varchar(5000) -- Used to build our Proper Case string for Function return
      declare @Word varchar(1000) -- Temporary storage for each word
      declare @IsWhiteSpace as bit -- Used to indicate whitespace character/start of new word
      declare @c char(1) -- Temp storage location for each character

      set @Word = ''
      set @i = 1
      set @IsWhiteSpace = 1
      set @ProperCaseText = ''
      set @sep = '|'

      -- Set default UPPERCASEWord Length
      if @UCASEWordLength is null set @UCASEWordLength = 1
      -- Convert user input to lower case (This function will UPPERCASE words as required)
      set @Value = LOWER(@Value)

      -- Loop while counter is less than text lenth (for each character in...)
      while (@i <= len(@Value)+1)
      begin

            -- Get the current character
            set @c = SUBSTRING(@Value,@i,1)

            -- If start of new word, UPPERCASE character
            if @IsWhiteSpace = 1 set @c = UPPER(@c)

            -- Check if character is white space/symbol (using ascii values)
            set @IsWhiteSpace = case when (ASCII(@c) between 48 and 58) then 0
                                          when (ASCII(@c) between 64 and 90) then 0
                                          when (ASCII(@c) between 96 and 123) then 0
                                          else 1 end

            if @IsWhiteSpace = 0
            begin
                  -- Append character to temp @Word variable if not whitespace
                  set @Word = @Word + @c
            end
            else
            begin
                  -- Character is white space/punctuation/symbol which marks the end of our current word.
                  -- If word length is less than or equal to the UPPERCASE word length, convert to upper case.
                  -- e.g. you can specify a @UCASEWordLength of 3 to automatically UPPERCASE all 3 letter words.
                  set @Word = case when len(@Word) <= @UCASEWordLength then UPPER(@Word) else @Word end

                  -- Check word against user exceptions list. If exception is found, use the case specified in the exception.
                  -- e.g. WiseSoft, RAM, CPU.
                  -- If word isn't in user exceptions list, check for "known" exceptions.
                  set @Word = case when charindex(@sep + @Word + @sep,@exceptions collate Latin1_General_CI_AS) > 0
                                    then substring(@exceptions,charindex(@sep + @Word + @sep,@exceptions collate Latin1_General_CI_AS)+1,len(@Word))
                                    when @Word = 's' and substring(@Value,@i-2,1) = '''' then 's' -- e.g. Who's
                                    when @Word = 't' and substring(@Value,@i-2,1) = '''' then 't' -- e.g. Don't
                                    when @Word = 'm' and substring(@Value,@i-2,1) = '''' then 'm' -- e.g. I'm
                                    when @Word = 'll' and substring(@Value,@i-3,1) = '''' then 'll' -- e.g. He'll
                                    when @Word = 've' and substring(@Value,@i-3,1) = '''' then 've' -- e.g. Could've
                                    else @Word end

                  -- Append the word to the @ProperCaseText along with the whitespace character
                  set @ProperCaseText = @ProperCaseText + @Word + @c
                  -- Reset the Temp @Word variable, ready for a new word
                  set @Word = ''
            end
            -- Increment the counter
            set @i = @i + 1
      end
      return @ProperCaseText
end