Problem solve Get help with specific problems with your technologies, process and projects.

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.

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

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close