I recently pulled in a table of zipcodes from an external source. Everything was in caps and I wanted the data formatted with the first letter of each word to be in caps. After a bit of digging around the net, I found this function somewhere that can help:
CREATE function FirstCaps(@textValue varchar(7999))
returns varchar(7999)
as
begin
declare @workText varchar(8000)
declare @newtxt varchar(8000), @startpos int, @nextpos int
set @workText = @textValue+' ';
select @startpos = 1 -- starting position
select @nextpos = CharIndex( ' ', @workText, @startpos)
Select @newtxt = ''
while @startpos <> 0 and @nextpos <> 0
begin
set @newTxt = @newTxt + upper(substring(@workText, @startpos,1)) +
Lower(substring(@workText, @startpos +1, @nextpos - @startpos))
set @Startpos = @nextpos + 1
set @nextpos = charIndex( ' ', @workText,@startpos )
end
return @newtxt
end
It is a bit crude, but works for what I needed!