LENGTH - Get String Length - Oracle to SQL Server Migration

In Oracle, LENGTH function returns the length of a string in characters as defined by the input character set. In SQL Server, you can use LEN function, but note that it excludes trailing blanks.

When applied to a CHAR or NCHAR column, Oracle LENGTH returns the maximum length of the column (defined in CREATE TABLE), while SQL Server LEN returns the actual data length.

Oracle Example:

  -- Get the length of string (returns 5)
  SELECT LENGTH('Spain') FROM dual;

SQL Server Example:

  -- Get the length of string (returns 5)
  SELECT LEN('Spain');

Oracle LENGTH to SQL Server conversion summary:

	Oracle	SQL Server
Syntax	LENGTH(string)	LEN(string)
Length in Characters
Trailing Spaces Included			Use DATALENGTH
CHAR and NCHAR Columns	Maximum column length returned	Actual data length returned	Use COL_LENGTH
Get Length in Bytes	LENGTHB function	DATALENGTH function

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

When Oracle LENGTH function is used for a variable-length column that does not contain trailing blanks, it can be converted to the SQL Server LEN function.

Oracle LENGTH function counts the trailing spaces:

Oracle:

  -- Trailing spaces are included (returns 7) 
  SELECT LENGTH('Greece ') FROM dual;

SQL Server LEN function excludes the trailing spaces:

SQL Server:

  -- Trailing spaces are excluded (returns 6) 
  SELECT LEN('Greece ');

To include the trailing spaces to the string length, you can use DATALENGTH function in SQL Server that returns the number of bytes, so you have to divide the result by 2 for Unicode strings (UCS-2 in SQL Server):

SQL Server:

  -- Trailing spaces are included (returns 7) 
  SELECT DATALENGTH('Greece ');
 
  -- Trying DATELENGTH with a Unicode string (returns 14)
  SELECT DATALENGTH(N'Greece '); 
 
  -- Using DATALENGTH to get the length in characters of a Unicode string (returns 7)
  SELECT DATALENGTH(N'Greece ')/2;

When Oracle LENGTH function is used with a CHAR or NCHAR column, it returns the maximum column size defined in the CREATE TABLE statement, not the actual data length:

Oracle:

  -- Define a table with a variable and fixed length column
  CREATE TABLE countries
  (
    name VARCHAR2(90),
    name2 CHAR(90)
  );
 
  -- Insert a sample row
  INSERT INTO countries VALUES ('Ukraine', 'Ukraine');
 
  -- Actual data length is returned for VARCHAR column (returns 7)
  SELECT LENGTH(name) FROM countries;
 
  -- Maximum column size is returned for CHAR column (returns 90)
  SELECT LENGTH(name2) FROM countries;

In SQL Server, LEN function returns the actual data length for both variable and fixed length columns:

SQL Server:

  -- Define a table with a variable and fixed length column
  CREATE TABLE countries
  (
    name VARCHAR(90),
    name2 CHAR(90)
  );
 
  -- Insert a sample row
  INSERT INTO countries VALUES ('Ukraine', 'Ukraine');
 
  -- Actual data length is returned for VARCHAR column (returns 7)
  SELECT LEN(name) FROM countries;
 
  -- Actual data length is also returned for CHAR column (returns 7)
  SELECT LEN(name2) FROM countries;

If you need to return the maximum column size, you can use COL_LENGTH function in SQL Server:

SQL Server:

  -- Get the maximum column size in bytes (returns 90)
  SELECT COL_LENGTH('countries', 'name2') FROM countries;

• Oracle to SQL Server Migration Knowledge Base

Oracle 11g R2 SQL Language Reference

• LENGTH Function

Microsoft SQL Server 2012 - Books Online

• LEN Function

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