Capitalize the First Letter of Each Word in a String in SQL Server

February 06, 2012 Blue Cloud SQL 0

In Oracle, there is a string function called InitCap to capitalize the first letter of the word in a string and lower the rest. Sql server does not have build-in function like that. User-defined function is the key to get that behavior. Here's the function to get such result.

Code Snippet

CREATE FUNCTION [dbo].[ufn_InitCap] 
(
	@InputString AS VARCHAR(MAX)
)
RETURNS VARCHAR(MAX) AS
BEGIN

SET @InputString = LOWER(ISNULL(@InputString,''))

DECLARE @Length INT
DECLARE @CharIndex INT
DECLARE @PChar AS CHAR(1)

SELECT @Length = LEN(@InputString), @CharIndex = 1 

IF(@Length > 0)
BEGIN
	
	WHILE @CharIndex <= @Length
	BEGIN
		SET @PChar = SUBSTRING(@InputString,@CharIndex-1,1)
		
		IF @PChar IN(' ','.','?',';','!') -- Add Any Char What You Want
			SET @InputString = STUFF(@InputString,@CharIndex,1,UPPER(SUBSTRING(@InputString,@CharIndex,1)))			

		SET @CharIndex = @CharIndex + 1
		
	END
	
END

	RETURN @InputString

END

Sample Usage

SELECT dbo.ufn_InitCap('capitalize the first letter of eAch woRd in a stRing in sQL SErver.')
--Output : Capitalize The First Letter Of Each Word In A String In Sql Server.

Note

@InputString : The string argument whose first character in each word will be converted to uppercase and all remaining characters converted to lowercase.

Author : Blue Cloud

Just a normal software developer who love to write code. Developing professional software since 1999. After working in the software industry for many years, I've started my own website to share knowledges and experiences.