SQL The correct option for handling function requests

Question

I have a table of names that I am trying to convert from UPPERCASE to the correct one. And the code below does pretty much what I am. When I tested this, I noticed that I had people who had Roman numerals in their name, Mc * and O '* in the table. Naturally, the query converted any plural Roman numeral to Iv, as expected, and any MCDONALDS or O'DANIEL were converted to Mcdonalds and O'daniel. I tried to figure out how to make a clean replacement for this function so that I could run my update request, but I still calmly understand my SQL knowledge after hours. Any help / suggestions would be greatly appreciated. I did a google search and found some examples, but the ones I tried did not work. The number of corrections I will need to makerelatively small (17 amendments from a table of 1000 rows), but I would like to try to put it in order to limit as many human errors as possible.

Thank you in advance for your help.

CREATE FUNCTION [dbo].[f_ProperCase]
(@Text as varchar(80))
RETURNS varchar(80) as
BEGIN

DECLARE @Reset bit
DECLARE @Ret varchar(80)
DECLARE @i int
DECLARE @c char(1)

SELECT @Reset = 1, @i=1, @Ret = ''

WHILE @i <= LEN(@Text)
SELECT @c= SUBSTRING(@Text,@i,1),
@Ret = @Ret + CASE WHEN @Reset=1 THEN UPPER(@c) ELSE LOWER(@c) END,
@Reset= CASE WHEN 
CASE WHEN SUBSTRING(@Text,@i-4,5) like '_[a-z] [DOL]''' THEN 1 
WHEN SUBSTRING(@Text,@i-4,5) like '_[a-z] [D][I]' THEN 1 
WHEN SUBSTRING(@Text,@i-4,5) like '_[a-z] [M][C]' THEN 1 
WHEN SUBSTRING(@Text,@i-4,5) like '_[a-z] [M][c][*]' THEN 1 --Convert MCDONALD to     McDonald
WHEN SUBSTRING(@Text,@I-4,5) like '_[a-z] [O][''][*]' THEN 1 --Convert O'DONNEL to O'Donnel
ELSE 0 
END = 1 
THEN 1 
ELSE CASE WHEN @c like '[a-zA-Z]' or @c in ('''') THEN 0 
ELSE 1 
END 
END,
@i = @i +1
RETURN @Ret
end

Answer 1

I would do it differently:

CREATE FUNCTION [dbo].[f_ProperCase]
(@Text as varchar(80))
RETURNS varchar(80) as
BEGIN

DECLARE @Reset bit
DECLARE @Ret varchar(80)
DECLARE @i int
DECLARE @c char(1)
DECLARE @Text1 varchar(81)

SELECT @Reset = 1, @i=1, @Ret = '', @Text1 = ' ' + @Text

WHILE @i <= LEN(@Text1)
    SELECT @c= SUBSTRING(@Text1,@i,1),
    @Ret = @Ret + CASE WHEN @Reset=1 THEN UPPER(@c) ELSE LOWER(@c) END,
    @Reset= CASE WHEN 
    CASE WHEN SUBSTRING(@Text1,@i-2,3) like ' [DdOoLl]''' THEN 1 
    WHEN SUBSTRING(@Text1,@i-2,4) like ' [Mm][cC][a-zA-Z]' THEN 1 
    WHEN SUBSTRING(@Text1,@i-3,5) like ' [Mm][Aa][cC][a-zA-Z]' THEN 1 
    ELSE 0 
    END = 1 
    THEN 1 
    ELSE CASE WHEN @c like '[a-zA-Z]' or @c in ('''') THEN 0 
    ELSE 1 
    END 
    END,
    @i = @i +1
RETURN stuff(@Ret, 1, 1, '')
end

This feature supports O ', L', D ', as well as Mc and Mac. A function is also converted from any case (and not just to uppercase) to the correct case

SQL Fiddle

select dbo.f_ProperCase('CORMACK')
      ,dbo.f_ProperCase('Mcdonald ronald')
      ,dbo.f_ProperCase('o''hara')

|         |                 |        |
|---------|-----------------|--------|
| Cormack | McDonald Ronald | O'Hara |