SQL funtion to Capitalize a string

If you need a string you select from a microsoft SQL database to be capitalized, you could use the the folowing select statement: SELECT dbo.InitCap([FieldToCapatalize]) FROM TableName

To make this work the function ‘InitCap’ should be made available by adding a new function to your database. To add a function in Microsoft SQL Server Management Studio; In the Object Explorer you navigate to the Databases > [DatabaseName] > Programmability > Functions > Right Click > New. Choose any of the three new function types you could add. Then replace all the the code in the editor window with the code below. Make sure to replace DataBaseName with the name of your database.

USE [DataBaseName]
GO
/****** Object: UserDefinedFunction [dbo].[InitCap] Script Date: 05/07/2010 16:26:51 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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

Leave a Reply

Your email address will not be published. Required fields are marked *