Case formatting on "Mc" names

[leftfldr]

Using SQL 2005 I have a contact name field where the first and last names are stored together. Some of the last names start with Mc. I need a script that will capitalize the letter after the Mc. Here are examples of data in the table.
Bill Mcconnell
Jim Mccall
Joe Smith
Tim Lansing
Dan Mcqueen

Any help would be appreciated.

 

[bborissov]

NOT TESTED AT ALL!!!!!!!!!!!!!!!

SELECT CASE WHEN CHARINDEX(' Mc', NameField) = 0
                 THEN NameField
            ELSE LEFT(NameField, CHARINDEX(' Mc', NameField)+2)+UPPER(SUBSTRING(NameField,CHARINDEX(' Mc', NameField)+3,1))+SUBSTRING(NameField,CHARINDEX(' Mc', NameField)+4,8000) END AS Name
FROM YourTable

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

BTW that should work ONLY if you have only First name and Last Name stored together, not if you have something like this:

Bill Mc Mcconnell
Bill Mcconnell Mcconnell
etc.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[whosrdaddy]

I think regex replace function is more appropriate in this case:

http://weblogs.sqlteam.com/jeffs/archive/2007/04/27/SQL-2005-Regular-Expression-Replace.aspx

Cheers,
Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!