Click here to Skip to main content
13,090,446 members (53,944 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


2 bookmarked
Posted 15 Apr 2011

Microsoft SQL Name capitalize function

, 18 Apr 2011
Rate this:
Please Sign up or sign in to vote.
Microsoft SQL Function To Proper Case A Name From A Given String
This is a basic function for Microsoft SQL to generate a proper case name and remove some of the spaces.

create function properCaseName ( @s varchar(255))
returns varchar(255)
declare @flag int,@retVal as varchar(255)
-- @flag is the flag variable
-- @retVal the output
-- if the incoming string is null then put an empty string
-- then replace the spaces (using prime numbers 7,5,3,2) so we get single spacing
-- and finally trim the whole thing on both sides
select @s=lower(ltrim(rtrim(replace(replace(replace(replace(isnull(@s,''),'      ',' '),'     ',' '),'   ',' '),'  ',' '))))
-- initialize the variables
select @retVal='',@flag=len(@s)
-- If the length of the incoming string ended up to be zero... skip the loop
while (@flag>0)
-- the flag will hold the index of the first space if any
-- if there are no spaces left, it becomes 0 hence being the last loop
    select @flag=charindex(' ',@s)
-- append the first character of the input string in upper case then
-- if there was a space, the substring of @flag characters - 1 (we have to take one
-- from @flag so we don't go beyond the space) starting from the second character 
-- (this is why we take the 1 off the count).
-- If there was no space left then append to the end of the reminder string.
    select @retVal=@retVal+upper(left(@s,1))+case @flag when 0 then right(@s,len(@s)-1) else substring(@s,2,@flag-1) end
-- Make the input string begin at the space+1 character
    select @s=right(@s,len(@s)-@flag)
-- Maybe this line isn't really required
select @retVal=ltrim(rtrim(@retVal))
return @retVal


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Web Developer
United States United States
This member doesn't quite have enough reputation to be able to display their biography and homepage.

You may also be interested in...

Comments and Discussions

-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170813.1 | Last Updated 18 Apr 2011
Article Copyright 2011 by cluengas2k
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid