Wednesday, 2 March 2011

Empty Space and Single Space Data Move

Empty Space strings, that is Strings with no text whats so ever '', are treated differently by different databases.
Oracle treats an empty string '' as NULL
Sybase treats an empty string '' as a single spaced string ' '
Some other database support empty strings

In the migration preferences you can decide how to handle empty strings.
You can either migrate them to
1) ' ' A single space.
or
2) NULL (which is how Oracle would interpret an empty string anyway)

There is one other thing to note.
JTDS 1.2 (the recommended JDBC driver for Sybase and SQL Server) returns an empty string even when the data in the Sybase or SQL Server database is a single space.
This is a bug in JTDS, but it should be noted that if you keep the default option of migrating empty strings to a single space, you should see no issue.