Teams Collaboration

Capitalizing the first letter of each word

I recently had a request from a client that wanted to capitalize the first letter of each word in the account description. I found that Oracle has a built in function for this but SQL Server does not. After some research I found a script that was useful and thought I would post it out here for others to use.

–This is the update function

update GL00100 set ACTDESCR = dbo.InitCap(ACTDESCR)

–This is the SQL Function

CREATE FUNCTION [dbo].[InitCap] ( @InputString varchar(4000) )

RETURNS VARCHAR(4000)

AS

BEGIN

DECLARE @Index INT

DECLARE @Char CHAR(1)

DECLARE @PrevChar CHAR(1)

DECLARE @OutputString VARCHAR(255)

SET @OutputString = LOWER(@InputString)

SET @Index = 1

WHILE @Index <= LEN(@InputString)

BEGIN

SET @Char = SUBSTRING(@InputString, @Index, 1)

SET @PrevChar = CASE WHEN @Index = 1 THEN ‘ ‘

ELSE SUBSTRING(@InputString, @Index – 1, 1)

END

IF @PrevChar IN (‘ ‘, ‘;’, ‘:’, ‘!’, ‘?’, ‘,’, ‘.’, ‘_’, ‘-‘, ‘/’, ‘&’, ””, ‘(‘)

BEGIN

IF @PrevChar != ”” OR UPPER(@Char) != ‘S’

SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))

END

SET @Index = @Index + 1

END

RETURN @OutputString

END

GO

–provided by Tim Lally KTL Solutions, Inc http://www.ktlsolutions.com

Share this post

Related Posts