Helpful Information

Category: MS SQL Development

User defined functions for proper case

I am trying to write a function that takes as its input, a proper name and returns the name in proper case. For example, I type in JON DOE and it returns Jon Doe, or jon doe and returns Jon Doe. Any help would be greatly appreciated.

Try a google.com search using "sql proper name". The first site found is "SQL Server Examples" by Greg Larson. See http://www.geocities.com/sqlserverexamples/string6.htm

This scriipt can be changed to a function:

create table #temp (
name varchar(100))
insert into #temp values('GREGORY ALAN LARSEN')
insert into #temp values('TODD JOSHUA SMITH')
select
-- Get First Character of First Name
upper(substring(name,1,1)) +
-- Get rest of first name
lower(substring(name,2,charindex(' ',name)-2)) + ' ' +
-- Get first Character of Middle Name
upper(substring(name,charindex(' ',name)+1,1)) +
-- Get rest of Middle Name
lower(substring(substring(name,charindex(' ',name)+2,len(name)),1,
charindex(' ',substring(name,charindex(' ',name)+2,len(name)))-1)) + ' ' +
-- Get First Character to Last Name
upper(substring(substring(name,charindex(' ',name)+2,len(name)),
charindex(' ',substring(name,charindex(' ',name)+2,len(name)))+1,1)) +
-- Get Rest of Last Name
lower(substring(substring(name,charindex(' ',name)+2,len(name)),
charindex(' ',substring(name,charindex(' ',name)+2,len(name)))+2,
len(substring(name,charindex(' ',name)+2,len(name)))))
from #temp
drop table #temp

Greg Larson's comments:
"This script will proper case the first, middle, and last name for a person. This script strips apart the first, middle and last name then uppercase the first character of each name, and lowercases the rest. This script only works if there is a single space between each names. This script uses the charindex function to identify the character offset of the space between each name. If multiple spaces exist they will need to be removed first (look at "remove white space between multiple words")."

Greg's routine to remove white space between multiiple words can be changed to locate the first white space and remove remaining white space rather than remove ALL white space.