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 first published in February 2005
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close