SQL-Server Blog of Ritesh Shah --Fight the fear of SQL with SQLHub.com

We may need to convert some text string to proper case (first letter of each word capital). I have one UDF to share with you guys for proper case. Have a look at it and enjoy the power of T-SQL programming.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

/*************************************************************************************************

Examples:

To convert from 'ritesh a shah' to 'Ritesh A Shah':

SELECT dbo.PROPCASE('ritesh a shah')

*************************************************************************************************/

CREATE FUNCTION [dbo].[PROPCASE](@in varchar(5000))

RETURNS varchar(8000)

AS

BEGIN

IF @in IS NULL

BEGIN

RETURN NULL

END

DECLARE @out varchar(8000)

DECLARE @i int, @len int, @found_at int

DECLARE @LCASE_a int, @LCASE_z int, @Delimiter char(3), @UCASE_A int, @UCASE_Z int

SET @i = 1

SET @len = LEN(@in)

SET @out = ''

SET @LCASE_a = 97

SET @LCASE_z = 122

SET @Delimiter = ' ,-'

SET @UCASE_A = 65

SET @UCASE_Z = 90

WHILE @i <= @len

BEGIN

WHILE CHARINDEX(SUBSTRING(@in,@i,1), @Delimiter) > 0

BEGIN

SET @out = @out + SUBSTRING(@in,@i,1)

SET @i = @i + 1

END

IF ASCII(SUBSTRING(@in,@i,1)) BETWEEN @LCASE_a AND @LCASE_z

BEGIN

SET @out = @out + UPPER(SUBSTRING(@in,@i,1))

END

ELSE

BEGIN

SET @out = @out + SUBSTRING(@in,@i,1)

END

SET @i = @i + 1

WHILE CHARINDEX(SUBSTRING(@in,@i,1), @Delimiter) = 0 AND (@i <= @len)

BEGIN

IF ASCII(SUBSTRING(@in,@i,1)) BETWEEN @UCASE_A AND @UCASE_Z

BEGIN

SET @out = @out + LOWER(SUBSTRING(@in,@i,1))

END

ELSE

BEGIN

SET @out = @out + SUBSTRING(@in,@i,1)

END

SET @i = @i + 1

END

END

RETURN @out

END

Note: I don’t remember whether I have written this script, modified it or got it somewhere from internet as I am using it since long time.

Reference: Ritesh Shah