SQL INITCAP Function Alternative in Azure Synapse and TSQL

The INITCAP function is one of the quite useful functions you can use to capitalize the first letter (CamelCase) of each word. The popular relational databases such as Oracle, Teradata, etc. support INITCAP function to represent strings in a CamelCase. But, same function will not work on the Microsoft SQL Server or Azure Synapse analytics data warehouse. Both SQL Server and Azure Synapse databases are based on TSQL. In this article, we will check what are the INITCAP function alternative in Azure Synapse and TSQL.

INITCAP Function Alternative in Azure Synapse and TSQL

Before going into alternate approaches, let us check what is INITCAP function.

SQL INITCAP Function

You can use the INITCAP function to create a CamelCase string. i.e. the first letter of each word in uppercase, all other letters in lowercase.

The following Teradata example capitalizes each word in the string. The same example will work with Oracle database as well.

SELECT INITCAP( 'This is a sample TEXT.') as INITCAP;
INITCAP
----------------------
This Is A Sample Text.

In the above examples, the first letter of each word is a capital letter.

The following list provides an INITCAP function alternative in SQL Server and Azure Synapse data warehouse.

Use Buil-in String Functions as an INITCAP Alternative
Create INITCAP User Defined Function

Now let us check these two approaches in brief.

TSQL Buil-in String Functions as an INITCAP Alternative

TSQL is supports many useful built-in string functions. The string functions such as STRING_AGG, STRING_SPLIT and STUFF can be used together as an INITCAP function alternative in Azure Synapse data warehouse and TSQL.

For example,

declare @word as nvarchar (50)
set @word = 'This is a sample TEXT.'
select string_agg(stuff(lower(value),1,1,upper(SUBSTRING(value,1,1))),' ') as INITCAP from STRING_SPLIT(@word, ' ');
INITCAP
-------
This Is A Sample Text.

This is the option if you don’t want to create or use the user defined function.

Create a TSQL INITCAP User Defined Function

Another best approach is to create an INITCAP user defined function in TSQL. This is one of the easy and reliable approach compared to traditional SQL approach.

The following TSQL user defined function works with both SQL Server as well as Azure Synapse.

CREATE FUNCTION [dbo].[InitCap] (@inStr [VARCHAR](8000)) 
RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @outStr VARCHAR(8000) = LOWER(@inStr),
		 @char CHAR(1),	
		 @alphanum BIT = 0,
		 @len INT = LEN(@inStr),
                 @pos INT = 1;		  
 
    -- Iterate through all characters in the input string
    WHILE @pos <= @len BEGIN
 
      -- Get the next character
      SET @char = SUBSTRING(@inStr, @pos, 1);
 
      -- If the position is first, or the previous character is not alphanumeric
      -- convert the current character to upper case
      IF @pos = 1 OR @alphanum = 0
        SET @outStr = STUFF(@outStr, @pos, 1, UPPER(@char));
 
      SET @pos = @pos + 1;
 
      -- Define if the current character is non-alphanumeric
      IF ASCII(@char) <= 47 OR (ASCII(@char) BETWEEN 58 AND 64) OR
	  (ASCII(@char) BETWEEN 91 AND 96) OR (ASCII(@char) BETWEEN 123 AND 126)
	  SET @alphanum = 0;
      ELSE
	  SET @alphanum = 1;
    END
   RETURN @outStr;		   
  END
GO

You can test your UDF by calling it by passing a string or table column.

For example,

SELECT dbo.INITCAP( 'This is a sample TEXT.') as INITCAP;"
INITCAP
-------
This Is A Sample Text.