Friday, April 29, 2011

How to i set the first char of Name Column always to capital?

CREATE FUNCTION ProperCase(@Input AS VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN

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


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


WHILE (@i <= LEN(@Input))


SELECT @c= SUBSTRING(@Input,@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  

 

DECLARE @A VARCHAR(10)
SET @A = 'abcde'
SELECT dbo.[ProperCase](@A)
(No column name)
Abcde