Convert a string to the proper case using SQL language

This user-defined function takes an input string and converts it to upper and lower case (or proper case) format.

This user-defined function takes an input string and converts it to upper and lower case (or proper case) format....

It can handle names like O'Brian and McMurray. It also standardizes P.O. Boxes.

Create FUNCTION UpperLowerCase (@name varchar(100)) RETURNS varchar(100) AS BEGIN declare @cnt smallint select @cnt = 2 select @name = ltrim(rtrim(@name)) if @name is null or len(@name) = 0 goto finish select @name = upper(substring(@name,1,1)) + lower(substring(@name,2,len(@name)-1)) WHILE @cnt < len(@name) BEGIN select @name = substring(@name,1, @cnt) + upper(substring(@name,@cnt+1,1)) + substring(@name,@cnt+2,len(@name)-(@cnt+1)) where @name is not null and @name <> ' ' and (substring(@name,@cnt,1) in (' ', '-', '/') or (substring(@name,@cnt,1) between '0' and '9' and substring(@name,@cnt+1,1) between 'a' and 'z' and substring(@name,@cnt+1,1) not in ('n','t','s','r')) or (substring(@name,@cnt,1) in ('.') and substring(@name,@cnt+1,1) not in (' ',',')) or substring(@name,@cnt-1,2) = 'mc' or (substring(@name,@cnt-1,1) = 'O' and substring(@name,@cnt,1) = '''')) select @cnt = @cnt + 1 END select @name = 'P.O. Box ' + substring(@name,8,len(@name)-7) where @name like 'Po Box%' and len(@name) > 7 select @name = 'P.O. Box ' + substring(@name,9,len(@name)-8) where @name like 'P O Box%' and len(@name) > 8 select @name = 'P.O. Box ' + substring(@name,17,len(@name)-16) where @name like 'Post Office Box%' and len(@name) > 16 finish: RETURN @name END 

What did you think about this tip? Email the editor with thoughts and questions: editor@searchsqlserver.com.

Next Steps

Ask your technical SQL Server questions -- or help out your peers by answering them -- in our discussion forums.

This was last published in February 2005

Dig Deeper on SQL-Transact SQL (T-SQL)

PRO+

Content

Find more PRO+ content and other member only offers, here.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close