Change the case on Mc surnames to proper case

Question

I run a function to update some columns on a few tables to change UPPERCASE to ProperCase. I didn't take into account the 'Mc' or 'Mac' surnames. So now all my McSurnames have changed to Mcsurnames.

Does anyone have any scripts that would amend to McSurname?

Any help appreciated.

Thanks.

My name is Machado and I come from Portugal, what will you do to my name? Or do you only have Irish and Scottish origin names? — Charlieface, Mar 11 '21 at 18:52
95% of names are Irish, only a few that may vary, which we can deal with individually within the application that uses the DB — MalcolmPH, Mar 11 '21 at 18:54
I haven't tried anything yet. just trying to see if there is a way that doesn't completely mess up the DB. — MalcolmPH, Mar 11 '21 at 21:22

score 0 · Accepted Answer · answered Mar 11 '21 at 18:56

This can be a slippery slope. You will find many exceptions. That said, here is a scaled down version of my proper function.

Example

Select [dbo].[svf-Str-Proper]('john old macdonald ii phd,dds llc')

Reurns

(No column name)
John Old MacDonald II PhD,DDS LLC

The Function if Desired

CREATE FUNCTION [dbo].[svf-Str-Proper] (@S varchar(max))
Returns varchar(max)
As
Begin
    Set @S = ' '+replace(replace(Lower(@S),'   ',' '),'  ',' ')+' '
    ;with cte1 as (Select * From (Values(' '),('-'),('/'),('\'),('['),('{'),('('),('.'),(','),('&'),(' Mc'),(' Mac'),(' O''') ) A(P))
         ,cte2 as (Select * From (Values('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M')
                                       ,('N'),('O'),('P'),('Q'),('R'),('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z')
                                       ,('LLC'),('PhD'),('MD'),('DDS')
                                       ,('II '),('III '),('IV ')
                                       ,('ID '),('PW ')
                                 ) A(S))
         ,cte3 as (Select F = Lower(A.P+B.S),T = A.P+B.S From cte1 A Cross Join cte2 B 
                   Union All 
                   Select F = Lower(B.S+A.P),T = B.S+A.P From cte1 A Cross Join cte2 B where A.P in ('&') 
                  ) 
    Select @S = replace(@S,F,T) From cte3 
    Return rtrim(ltrim(@S))
End

Thanks John, worked a treat - do you have a link to your full proper case script? — MalcolmPH, Mar 13 '21 at 13:25
@MalcolmPH Sorry, my proper function is actually linked to a table so that we can maintain exceptions — John Cappelletti, Mar 13 '21 at 14:38

Charlieface · Answer 2 · 2021-03-11T20:22:11.940

0

If we start by putting the prefix in a variable.

Then we need to filter the table so that it has at least two characters after that.

We update the table, so that the new value is the prefix, followed by the uppercase of the next letter, followed by the rest of the original value.

DECLARE @prefix nvarchar(100) = 'Mc'; -- or Mac

UPDATE table
SET lastname = @prefix + UPPER(SUBSTRING(lastname, LEN(@prefix) + 1, 1)) + SUBSTRING(lastname, LEN(@prefix) + 2, LEN(lastname)
WHERE lastname LIKE @prefix + '[a-z][a-z]%';

You can run this twice with the two different prefixes.

edited Mar 11 '21 at 20:22

answered Mar 11 '21 at 19:05

Charlieface

52,284
6
19
43

Would be a much better answer if you actually described what it does. You know that code-only answers are frowned upon. – SMor Mar 11 '21 at 20:06
so prefix all Mcxxxxxx with Mc = McMcxxxxxxx then replace McMc with McX?? – MalcolmPH Mar 11 '21 at 21:23
No, we skip the first few letters (the length of the prefix). It becomes `'Mc' + 'X' + 'xxxxxx'` – Charlieface Mar 11 '21 at 21:24

Change the case on Mc surnames to proper case

2 Answers2