Inserting Empty String to Numeric Column - Oracle to SQL Server Migration

In Oracle, if you insert an empty string ('') to a NUMBER column, Oracle inserts NULL.

In SQL Server, if you insert an empty string ('') to an integer column (INT i.e.), SQL Server inserts 0, if you insert an empty string to a decimal column (DECIMAL i.e.), the statement fails.

Summary:

Oracle NUMBER Column SQL Server INT Column SQL Server DECIMAL Column
Insert Empty String ('') NULL Inserted 0 Inserted Fails

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

Applications that Use String Literals for Numeric Data

Some applications can use character data types to store values entered by a user, including numeric data, and enclose them with single quotes before inserting into a table.

If the user did not specify any value, the application can attempt to insert an empty string ('') to the numeric column.

Oracle Inserts NULL When Empty String is Inserted to a NUMBER Column

Oracle allows you to use a string literal containing a numeric value to insert data into a NUMBER column without explicit data type casting.

But if you attempt to insert an empty string ('') to a NUMBER column, Oracle inserts NULL.

Oracle:

  -- Table definition
  CREATE TABLE airports 
  (
     name VARCHAR2(90),
     iata_code CHAR(3),
     domestic_rank NUMBER(5),
     world_rank NUMBER(5)
  );
 
  -- Inserting string literal and empty string to NUMBER columns
  INSERT INTO airports VALUES ('San Francisco International Airport', 'SFO', '10', '');
  # 1 rows inserted.

You can see that Oracle inserted 10 and NULL values into NUMBER columns:

Oracle converted '10' to 10 and '' to NULL (since in Oracle empty string is equivalent to NULL).

SQL Server Inserts 0 When Empty String is Inserted to Integer Column

Similar to Oracle, SQL Server also allows you to use a string literal to insert a value into an integer column (INT data type i.e.).

But if you insert an empty string to a INT column, SQL Server inserts 0, not NULL .

SQL Server :

  -- Table definition
  CREATE TABLE airports 
  (
     name VARCHAR(90),
     iata_code CHAR(3),
     domestic_rank INT,
     world_rank INT
  );
 
  -- Inserting string literal and empty string to INT columns
  INSERT INTO airports VALUES ('San Francisco International Airport', 'SFO', '10', '');
  # 1 row(s) affected

You can see that SQL Server inserted 10 and 0 values into INT columns:

SQL Server Raises an Error When Empty String is Inserted to a Decimal Column

If you insert an empty string to a decimal column in SQL Server, the insert statement fails:

SQL Server :

  -- Table definition (using DECIMAL data type instead of INT)
  CREATE TABLE airports 
  (
     name VARCHAR(90),
     iata_code CHAR(3),
     domestic_rank DECIMAL(5,0),
     world_rank DECIMAL(5,0)
  );
 
  -- Inserting string literal and empty string to DECIMAL columns
  INSERT INTO airports VALUES ('San Francisco International Airport', 'SFO', '10', '');
  # Msg 8114, Level 16, State 5, Line 1
  # Error converting data type varchar to numeric.

For a DECIMAL data type, you have to explicitly specify 0 or NULL instead of the empty string.

Resources

SQLines Services

SQLines offers database administration, optimization and migration services for Oracle and SQL Server databases and applications. For more information, please Contact Us.

Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - August 2012.

You could leave a comment if you were logged in.