InitCap functionality in SQL Server

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 372
SQL Server doesn't have in-built function to do InitCap functionality i.e. captializing first letter of each word in a string...
Below is the user-difined function to do so,

GO
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

--Test
SELECT dbo.[InitCap]( 'safjsal adoaskd ps') InitCap


/*
Result

InitCap
Safjsal Adoaskd Ps
*/

Comments or Responses

Login to post response