Função title case para SQL Server

Função title case para SQL Server

<SPAN >O usuário do SQL Server não tem nenhuma função para Title Case (tornar a primeira<BR></FONT></FONT></SPAN><SPAN >letra de cada palavra em maiúsculo), como a função do "initcap" no oracle.<SPAN > </SPAN>A função abaixo<BR></FONT></FONT></SPAN><SPAN >torna maiúscula a primeira letra de cada palavra dada uma string de texto:</FONT></FONT></SPAN>

<SPAN ></FONT></SPAN>

<SPAN ></FONT></SPAN>

<SPAN lang=EN-US >create function initcap (@text varchar(4000))<BR></FONT></FONT></SPAN><SPAN lang=EN-US >returns varchar(4000)<BR></FONT></FONT></SPAN><SPAN lang=EN-US >as</FONT></FONT></SPAN>

<SPAN lang=EN-US ></FONT></SPAN>

<SPAN lang=EN-US >begin<BR></FONT></FONT></SPAN><SPAN lang=EN-US >declare @counter int, <BR></FONT></FONT></SPAN><SPAN lang=EN-US >@length int,<BR></FONT></FONT></SPAN><SPAN lang=EN-US >@char char(1),<BR></FONT></FONT></SPAN><SPAN lang=EN-US >@textnew varchar(4000)</FONT></FONT></SPAN><SPAN lang=EN-US > </FONT></SPAN>

<SPAN lang=EN-US >set @text= rtrim(@text)<BR></FONT></FONT></SPAN><SPAN lang=EN-US >set @text= lower(@text)<BR></FONT></FONT></SPAN><SPAN lang=EN-US >set @length <SPAN >  </SPAN>= len(@text)<BR></FONT></FONT></SPAN><SPAN lang=EN-US >set @counter <SPAN > </SPAN>= 1</FONT></FONT></SPAN>

<SPAN lang=EN-US ></FONT></SPAN>

<SPAN lang=EN-US >set @text = upper(left(@text, 1) ) + right(@text, @length - 1) </FONT></FONT></SPAN><SPAN lang=EN-US > </FONT></SPAN>

<SPAN lang=EN-US ></SPAN><SPAN >while @counter <> @length --+ 1<BR></SPAN><SPAN ></SPAN><SPAN lang=EN-US >begin<BR></SPAN><SPAN lang=EN-US >select @char = substring(@text, @counter, 1)</FONT></FONT></SPAN>

<SPAN lang=EN-US ></FONT></SPAN>

<SPAN lang=EN-US >IF @char = space(1)<SPAN >  </SPAN>or @char =<SPAN >  </SPAN>'_' or @char = ','<SPAN >  </SPAN>or @char = '.' or @char = '\'<BR></FONT></FONT></SPAN><SPAN lang=EN-US > or @char = '/' or @char = '(' or @char = ')'<BR></FONT></FONT></SPAN><SPAN lang=EN-US >begin<BR></FONT></FONT></SPAN><SPAN lang=EN-US >set @textnew = left(@text, @counter)<SPAN >  </SPAN>+ upper(substring(@text, <BR></FONT></FONT></SPAN><SPAN lang=EN-US >@counter+1, 1)) + right(@text, (@length - @counter) - 1)<BR></FONT></FONT></SPAN><SPAN lang=EN-US >set @text = @textnew<BR></FONT></FONT></SPAN><SPAN lang=EN-US >end</FONT></FONT></SPAN>

<SPAN lang=EN-US ></FONT></SPAN>

<SPAN lang=EN-US >set @counter = @counter + 1<BR></FONT></FONT></SPAN><SPAN lang=EN-US ></SPAN><SPAN >end</SPAN>

<SPAN ></FONT></SPAN>

<SPAN >return @text<BR></FONT></FONT></SPAN><SPAN >end</FONT></FONT></SPAN>