Click here to Skip to main content
Click here to Skip to main content

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)
as
begin
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)
begin
-- 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)
end
-- Maybe this line isn't really required
select @retVal=ltrim(rtrim(@retVal))
return @retVal
end

License

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

About the Author

cluengas2k
Web Developer
United States United States
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 18 Apr 2011
Article Copyright 2011 by cluengas2k
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid