Capitalizing First Letter, and Lower the rest

I am trying to get this code to work: (left(religion,1))+ lower(right(religion,len(religion)-1))As Religion

I am getting this error:

Invalid length parameter passed to the RIGHT function.

This error occurs because you have a record that does not have religion specified (it's either NULL or an empty string). You can test for this using the case statement. When the length of the religion field is 0, then an empty string will be returned. If it's not zero, then your code kicks in and the first letter is capatalized.

Case When Len(religion) = 0 then '' else (left(religion,1))+ lower(right(religion,len(religion)-1)) end As Religion

|||

Ray,

Please try this function or set of functions. The first one is ProperWord and the other is ProperString. One calls the other, but if you are only doing one word, then you can just use ProperWord. Feel free to email me for the formatted version of the code.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[ProperWord](@.str VARCHAR(100))

RETURNS VARCHAR(100)

AS

BEGIN

DECLARE @.v_Len INT,

@.v_Value VARCHAR(100)

SET @.v_Len = LEN(@.str)

IF @.v_Len > 1

SET @.v_Value = UPPER(LEFT(@.str,1)) + LOWER(RIGHT(@.str,@.v_len - 1))

ELSE

SET @.v_Value = UPPER(@.str)

RETURN @.v_Value

END

GO

ALTER FUNCTION [dbo].[ProperString](@.str VARCHAR(MAX))

RETURNS VARCHAR(MAX)

AS

BEGIN

DECLARE @.v_Len INT,

@.v_Value VARCHAR(MAX),

@.v_Word VARCHAR(100),

@.v_Pos INT

SET @.v_Pos = CHARINDEX(' ', @.str)

WHILE @.v_Pos > 0

BEGIN

SELECT @.v_Len = LEN(@.str),

@.v_Word = LTRIM(RTRIM(LEFT(@.str,@.v_Pos))),

@.str = LTRIM(RTRIM(RIGHT(@.str, @.v_Len - @.v_Pos))),

@.v_Word = dbo.ProperWord(@.v_Word),

@.v_Value = COALESCE(@.v_Value + ' ' + @.v_Word, @.v_Word),

@.v_Pos = CHARINDEX(' ', @.str)

END

SELECT @.v_Word = dbo.ProperWord(LTRIM(RTRIM(@.str))),

@.v_Value = COALESCE(@.v_Value + ' ' + @.v_Word, @.v_Word)

RETURN @.v_Value

END

|||

Forgot the UPPER :)

declare @.religion varchar(100)
set @.religion = 'a'
select Case When Len(@.religion) = 0 then '' else (upper(left(@.religion,1)))+ lower(right(@.religion,len(@.religion)-1)) end As Religion

|||Mastros this worked great, everyone else thanks too.