Same Userdefined Function, tried with PATINDEX.

CREATE FUNCTION [CapitalCase]

(

@Input varchar(255)

)

RETURNS varchar(255)

AS

BEGIN

DECLARE @Results varchar(255)

Declare @NextWord int, @NextSpace int

if len(@Input)>0

Begin

-- Trimming the input

Set @Input = lower(ltrim(rtrim(@Input)))

-- Finding next word in the input

Set @NextWord = patindex('%[a-zA-Z]%',@Input)

-- Initializing the result

Set @Results = ''

-- If there is a word beginning, then make it Capital case

While @NextWord <> 0

Begin

-- Capital casing first character of first word and adding to result

Set @Results = @Results + UPPER(substring(@Input,@NextWord,1))

-- finding next word from next space

Set @NextSpace = charindex(char(32),@Input,1)

-- If there is no space then words are over, write till the end of string to result and break out of the loop

If @NextSpace = 0

BEGIN

SET @Results = @Results +SUBSTRING(@Input,@NextWord+1,LEN(@Input))

BREAK

END

-- If space is present then there might be one more word existing

ELSE

BEGIN

-- Set result set from the capital case character till the next space

SET @Results = @Results + SUBSTRING(@Input,@NextWord+1,(@NextSpace - @NextWord))

-- Making input as remaining part of Input from the next space

Set @Input = SUBSTRING(@Input,@NextSpace+1,LEN(@Input))

Set @NextWord = patindex('%[a-zA-Z]%',@Input)

END

End

End

Else

Set @Results= ''

Return @Results

END