KTL SOLUTIONS

Capitalizing the first letter of each word

Share this post

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

Related Posts

Checking Your CMMC Progress

Written by Alec Toloczko With Cybersecurity Maturity Model Certification (CMMC) requirements on the horizon, it’s crucial for organizations handling Controlled Unclassified Information (CUI) to adhere

Read More »