Search code examples
ado.netsqlcommanddbdatareader

Why does ExecuteReader() pad strings with traling spaces?

I have two queries:

Q1: select name from presidents where country = 'USA'
Q2: select 'Obama' as name from presidents where country = 'USA'

When using System.Data.Odbc.OdbcCommandExecuteReader then the returned DbDataReader contains 'Obama' in case of Q1 and 'Obama ' in case of Q2.

Why is the string padded with trailing spaces in case of Q2 and what is the remedy?

Trimming is ugly and even wrong in some cases. I am using .Net Framework 3.5.

Here is the test code

OdbcCommand cmd = new OdbcCommand();
cmd.Connection = (OdbcConnection)DatabaseContext.Connection;
cmd.CommandText = "select 'Obama' from dual";
cmd.CommandType = CommandType.Text;
OdbcDataReader r = cmd.ExecuteReader();
if (r.Read())
{
    String s = r.GetString(0);
    // s now contains "Obama          "
    // with trailing spaces
}
Solution

  • ExecuteReader returns data from the underlying data source without doing any padding.

    It's your underlying data source, in this case Oracle, that is returning trailing spaces.

    Google for "oracle trailing spaces" to understand why this is, and in particular to understand the difference between CHAR and VARCHAR data types in Oracle.

    Meanwhile, you can either remove the trailing spaces in the SQL query:

    SELECT RTRIM(Col1) FROM ...

    Or remove them in the client:

    string s = r.GetString(0).TrimEnd()