SQL UpperCase Third Character in Column - SQL Server 2005

I am trying to upper case the 3rd character in a column
where the first two characters are 'Mc%'

Something like:

update customer
set last_name =
UCase(Left(last_name,3))
where last_name like 'Mc%'


Can someone please verify my code?

Thanks!
robthomas09Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
halfbloodprinceConnect With a Mentor Commented:
THIS WORKS:

update customer
set last_name = Left(last_name,2) + Upper(SubString(last_name,3,1)) + SubString(last_name,4,len(last_name))
where last_name like 'Mc%'
0
 
chapmandewConnect With a Mentor Commented:
something like this:

update customer
set last_name = left(last_name, 3) + upper(substring(last_name, 3, 1)) + substring(last_name, 4, len(last_name))
where last_name like 'Mc%'
0
 
LowfatspreadConnect With a Mentor Commented:

update customer
set last_name = 'Mc' + upper(substring(last_name, 3, 1)) + substring(last_name, 4, len(last_name))
where last_name like 'Mc[a-z]%'
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
mavalphaConnect With a Mentor Commented:
Left(last_name,3) would return the first three characters together, not just the 3rd character by itself.  Plus, you're truncating the rest of your last name, so "Mcdonald" would become "MCD" and nothing else.  You need several substrings to accomplish this.  ("Mc", third letter, and everything else).  You could try the string "Replace" function, but this runs the risk of accidental replacements if you don't anticipate duplicate letters in the person's name.
0
 
chapmandewCommented:
whoops..you're right:

update customer
set last_name = left(last_name, 2) + upper(substring(last_name, 3, 1)) + substring(last_name, 4, len(last_name))
where last_name like 'Mc%'
0
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:

update customer
set last_name = STUFF(last_name, 3,1, UPPER(SUBSTRING(last_name,3,1) ) )
where last_name like 'Mc%'
0
 
robthomas09Author Commented:
Thanks everyone - I'll try it out now!
0
All Courses

From novice to tech pro — start learning today.