INITCAP - Capitalize the First Letter - Oracle to SQL Server Migration

In Oracle, INITCAP function capitalizes the first letter of each word and lowercases all other letters in a string. Words are delimited by a space or any other non-alphanumeric letter.

In SQL Server you can use a user-define function (UDF) to implement this functionality (see below).

Oracle:

  -- Capitalize the first letter of each word (returns: New York)
  SELECT INITCAP('new york') FROM dual;

SQL Server:

  -- Capitalize the first letter of each word (returns: New York) -  you can find the source code of UDF below
  SELECT dbo.InitCap('new york');

Note that SQL Server requires the schema name qualifier to call a UDF.

Oracle INITCAP to SQL Server conversion summary:

	Oracle	SQL Server
Syntax	INITCAP(string)	dbo.InitCap(string) - User-defined function
Uppercase First Letter
Lowercase Other Letters
Word Delimiter	Non-alphanumeric letter	Non-alphanumeric letter with ASCII code < 127

Last Update: Oracle 11g R2 and Microsoft SQL Server 2012

In Oracle, INITCAP function capitalizes the first letter of each word, and lowercases all other letters, and any non-alphanumeric is considered as the word delimiter.

Oracle:

  SELECT INITCAP('new/yORK') FROM dual;
  -- Returns: New/York

In SQL Server, you can use the following user-defined function to implement this functionality:

SQL Server:

  -- Drop the function if it already exists
  IF OBJECT_ID('dbo.InitCap') IS NOT NULL
	DROP FUNCTION dbo.InitCap;
  GO
 
 -- Implementing Oracle INITCAP function
 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 characater 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

Note that this UDF considers non-alphanumeric characters up to ASCII code 127 as a word delimiter, and you can easily extend it to support other characters if needed.

To call a UDF you have to specify the schema qualifier in SQL Server:

SQL Server:

  SELECT dbo.InitCap('new/yORK');
  -- Returns: New/York

• Oracle to SQL Server Migration Knowledge Base

Oracle 11g R2 SQL Language Reference

• INITCAP Function

SQLines offers services to migrate Oracle databases and applications to Microsoft SQL Server. For more information, please Contact Us.