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.

INITCAP Conversion Overview

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

Converting INITCAP from Oracle to SQL Server

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

Resources

SQLines Services

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

Discussion

, February 14, 2014 5:14 am

For different languages (such as ı - i problem in Turkish), the usage is like this:

select NLS_INITCAP('altını şer incisi ker', 'NLS_SORT = XTURKISH') from dual;

You could leave a comment if you were logged in.