none
Correct implementation of the Proper function to work with accents RRS feed

  • Pergunta

  • Hi everybody,

    It is quite strange that this problem didn't get detected before, but anyway. My colleague found the problem and I was able to trace it to this SQL Server function:

    CREATE function dbo.Proper (@cStringToProper varchar(max))
    returns varchar(max)
    as
    begin
       declare  @Position int;
       -- Add a space to the beginning to catch things if only a last name is passed
        set @cStringToProper = ' ' + @cStringToProper;
       --** Handle O', Mc and Mac names 
       select @cStringToProper = replace(replace(replace(replace(replace(replace(replace(replace(@cStringToProper
                          , '-',       char(1) + ' ')
                          , '''S ',    char(2) + ' ')
                          , '''',      char(3) + ' ')
                          , ' MC',     char(4) + ' ')
                          , ' MAC',    char(5) + ' ')
                          , ' VAN',    char(6) + ' ')
                          , ' VON',    char(7) + ' ')
                          , ' VANDER', char(8) + ' ');
       
       
       select @cStringToProper = stuff(lower(@cStringToProper) , 1 , 1 , upper(left(@cStringToProper , 1)))
            , @Position = patindex('%[^a-zA-Z][a-z]%' , @cStringToProper collate Latin1_General_Bin);
       
       while @Position > 0
             select @cStringToProper = stuff(@cStringToProper, @Position, 2, upper(substring(@cStringToProper, @Position, 2)))
                  , @Position = patindex('%[^a-zA-Z][a-z]%', @cStringToProper collate Latin1_General_Bin);
       
       --** -- Add O', Mc and Mac names back in
       select @cStringToProper = replace(replace(replace(replace(replace(replace(replace(replace(@cStringToProper
                          , char(8) + ' ', ' Vander')
                          , char(7) + ' ', ' Von')
                          , char(6) + ' ', ' Van')
                          , char(5) + ' ', ' Mac')
                          , char(4) + ' ', ' Mc')
                          , char(3) + ' ', '''')
                          , char(2) + ' ', '''s ')
                          , char(1) + ' ', '-');
       
       -- remove the space we added at the beginning ...
        set @cStringToProper = ltrim(@cStringToProper);
       return @cStringToProper;
    end
    go

    This function doesn't work properly if the name has accents, for example:

    Nina Nuñez, Sofia Días, and Jorge Aragão

    Do you see a way to fix this function?

    Thanks in advance.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    segunda-feira, 25 de novembro de 2019 13:46
    Moderador

Respostas

  • Try an adjustment of original function too:

    create function dbo.Proper2( @cStringToProper varchar(max))
    returns varchar(max)
    as
    begin
    	declare @Position int
    
    	set @cStringToProper = ' ' + lower(@cStringToProper)
    
    	set @cStringToProper = replace(replace(replace(replace(replace(replace(replace(replace(@cStringToProper,
    			'-',       char(1) + ' '), 
    			'''s ',    char(2) + ' '),
    			'''',      char(3) + ' '),
    			' mc',     char(4) + ' '),
    			' mac',    char(5) + ' '),
    			' van',    char(6) + ' '),
    			' von',    char(7) + ' '),
    			' vander', char(8) + ' ')
    
    	set @Position = charindex(' ', @cStringToProper)
    	while @Position <> 0 and @Position < len(@cStringToProper)
    	begin
    		set @cStringToProper = stuff( @cStringToProper, @Position + 1, 1, upper(substring( @cStringToProper, @Position + 1, 1)))
    		set @Position = charindex(' ', @cStringToProper, @Position + 1)
    	end
    
    	set @cStringToProper = replace(replace(replace(replace(replace(replace(replace(replace(@cStringToProper,
    			char(8) + ' ', ' Vander'),
    			char(7) + ' ', ' Von'),
    			char(6) + ' ', ' Van'),
    			char(5) + ' ', ' Mac'),
    			char(4) + ' ', ' Mc'),
    			char(3) + ' ', ''''),
    			char(2) + ' ', '''s '),
    			char(1) + ' ', '-')
    
    	return ltrim(@cStringToProper)
    end

    And maybe consider nvarchar type.


    • Editado Viorel_MVP segunda-feira, 25 de novembro de 2019 19:24
    • Marcado como Resposta Naomi NModerator segunda-feira, 25 de novembro de 2019 19:41
    segunda-feira, 25 de novembro de 2019 19:12

Todas as Respostas