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

Webinar Recap: CMMC News for the DIB

The DoD announcement on Nov 4 “Strategic Direction for the CMMC Program” provided new guidelines for CMMC 2.0. Our KTL webinar on Nov 10 helped to clarify these changes and answer questions for those in the DIB.

Read More »