Sql-server – Remove nulls from TSQL connection string

dnsempty stringnullodbcsql-server-2005

I have a bunch of queries, against a couple different databases, that I use to generate delimited reports. I'm trying to create some new reports against a MSSQL database and I'm having trouble replicating our other processes to get consistent results.

When I query against the other sources I get '' and 0 in place of nulls. When I query against MSSQL I get the nulls.

I'm using DSN strings => ODBC => remote database for some queries… and straight connection strings for other queries.

So against a DB2 database:

SELECT OrderID, CustomerName
FROM OrderTable LEFT JOIN CustomerTable on OrderCustomerID = CustomerID

Assume the CustomerTable doesn't have a Customer, so for OrderID 1, no customerID the result would be (output as a | delimted list):

0|Joe Schmoe
1|NULL
2|Jane Doe

When using DB2, I get "1|". When using MSSQL, I get "1|null".

Using ODBC, connection strings, server settings… how can I return "" or 0 on nulls to get the output to match our other processes?

I've tried a straight connection (no DSN/ODBC). I've tried different settings in ODBC. All three SQL drivers (SQL Server, SQL Native Client, SQL Server Native Client 10.0). All are coming back with NULL instead of "default" for the data type.

Update/Clarification
I'm working with an "in house" ETL program that pulls and transforms data from in-house databases for export to other institutions/customers mainly via delimited text. Previously all of the queries I've used, when hit with nulls, have simply output ""/0. MSSQL obviously doesn't do that, so I'm trying to mimic the other sources at the most generic level I can.

I understand how IsNull/Colesce works, and if need be, I will use these to get the job done. The obvious issue with using IsNull/Colesce is the added code, across what will be many queries. Added code means harder maintenance, more creation time, more points of failure, etc. If I can simply tell the connection level to replace all nulls, I would think it'd be much easier… but that makes the question Is it possible? and if so how? Either in the SQL2005 Server instance, or on the other side of the connection at the ODBC connection or connection string.

Best Answer

SQL Server always returns NULLs in this case. I know of no way to globally (through server settings or connection strings) cause it to return empty strings or zeroes instead of NULLs. And I do not think it is a database library issue--using different drivers won't change a thing. This is just the way SQL Server behaves.

If you want to convert NULLs to something else, you'll need to explicitly use the IsNull(Expression, ValueIfNull) function or the similar Coalesce() function which takes multiple parameters and returns the first non-null value.

SELECT O.OrderID, IsNull(CustomerName, '') CustomerName
FROM
   dbo.OrderTable O
   LEFT JOIN dbo.CustomerTable C
      ON O.OrderCustomerID = C.CustomerID

Though one comment... why would there be an order with no customer? Wouldn't a customer with no order be more likely?

Update

Can you put in a wrapper that, client side, converts the NULLs to what you want? What process or program are you using to convert the result sets to text?

Related Question