InitCap Function in SQL Server

There is no InitCap function in SQL Server. Who are mainly experienced in Oracle but have to work in SQL Server he/she may be depressed seeing the missing function like InitCap. But don’t worry. I am here to help you in this regard. I have created a function for you. Just execute the below script and enjoy InitCap function in SQL Server.

CREATE FUNCTION [dbo].[InitCap] ( @iString VARCHAR(5000) ) 
RETURNS VARCHAR(5000)
AS
BEGIN
DECLARE @tIndex          INT
DECLARE @tChar           CHAR(1)
DECLARE @pChar       CHAR(1)
DECLARE @oString   VARCHAR(5000)

SET @oString = LOWER(@iString)
SET @tIndex = 1

WHILE @tIndex <= LEN(@iString)
BEGIN
    SET @tChar     = SUBSTRING(@iString, @tIndex, 1)
    SET @pChar = CASE WHEN @tIndex = 1 THEN ' '
                         ELSE SUBSTRING(@iString, @tIndex - 1, 1)
END
IF @pChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
    BEGIN
        IF @pChar != '''' OR UPPER(@tChar) != 'S'
            SET @oString = STUFF(@oString, @tIndex, 1, UPPER(@tChar))
    END
    SET @tIndex = @tIndex + 1
END
RETURN @oString
END
GO

Now let’s see how to call the function from the SQL Query:

SELECT dbo.InitCap(profession_name) Profession,count(1) Customer_Count
FROM tblt_campaign_101_details
GROUP BY dbo.InitCap(profession_name)
ORDER BY 1