Wednesday, August 24, 2011

Proper Case t-sql

ALTER FUNCTION ProperCase(@Text VARCHAR (8000))
RETURNS VARCHAR(8000)
AS
BEGIN
/***************************************************

2011-08-24 10:44:23.503

Source: http://weblogs.sqlteam.com/jeffs/archive/2007/03/09/60131.aspx
***************************************************/


DECLARE @Reset BIT
DECLARE @Ret VARCHAR(8000)
DECLARE @i INT
DECLARE @c CHAR (1)

SELECT @Reset = 1, @i=1, @Ret = '';

WHILE (@i <= LEN (@Text))
SELECT @c=
SUBSTRING (@Text,@i,1)
, @Ret = @Ret
+ CASE
WHEN @Reset=1
THEN UPPER(@c)
ELSE LOWER(@c)
END
, @Reset = CASE
WHEN @c LIKE '[a-zA-Z]'
THEN 0
ELSE 1
END
, @i = @i +1
RETURN @Ret

END

No comments: