Monday, April 16, 2012

SqlServer Title Case

Some time ago, I've published a post (HERE) where it was written hot to capitalize strings in SQL Server.

Now I want to show how to capitalize every word in a string.

This topic is more complex, it's necessary to create a User Defined Function that does the work for us:


CREATE FUNCTION dbo.CapitalizeEveryWord(@input NVARCHAR(4000)) RETURNS NVARCHAR(4000)
AS 
BEGIN
DECLARE @position INT
WHILE IsNull(@position,Len(@input)) > 1
SELECT @input = Stuff(@input,IsNull(@position,1),1,upper(substring(@input,IsNull(@position,1),1))),
@position = charindex(' ',@input,IsNull(@position,1)) + 1
RETURN (@input)
END


It's done! Now just invoke that function:

SELECT dbo.CapitalizeEveryWord (Lower(ColumName)) FROM TableName