February 15, 2006

UDF For Capitalising First Letter in SQL Server 2000

This is a User Defined Function that will capitalize the first letter of a character after a space.
Also lowercase all capitalized words.
'MY NAME IS ROSHAN' --> 'My Name Is Roshan'.
Usage Select Capitalise('MY NAME IS ROSHAN')

-------------------------------------------------------



CREATE FUNCTION
Capitalise (@String VARCHAR(255))
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @StringCount int
SET @string = LOWER(@string)
SET @string = STUFF(@string,1,1,LEFT(UPPER(@string),1)) --Capitalize
the first letter
SET @StringCount = 0
WHILE @StringCount < LEN(@string)
BEGIN
IF SUBSTRING(@string,CHARINDEX(SPACE(1),@string,@StringCount),1)
= SPACE(1)
BEGIN
SET @string = STUFF(@string,CHARINDEX(SPACE(1),@string,@StringCount)+1,1,SUBSTRING(UPPER(@string),CHARINDEX(' ',@string,@StringCount)+1,1))
END
SET @StringCount = @StringCount + 1
END
RETURN @string
END

No comments:

Post a Comment