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:
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
SQLines offers services to migrate Oracle databases and applications to Microsoft SQL Server. For more information, please Contact Us.
Discussion
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;