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 ('') | ![]() | ![]() | ![]() |
Last Update: Oracle 11g R2 and Microsoft SQL Server 2012.
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 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).
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:
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.
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.