Remove nulls from TSQL connection string

Question

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.

Answer 1

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?

• WernerCD: The reason to change this at the driver level is simply scale... Some of the queries that get dumped to text files have 100+ columns and, while possible to IsNull or COALESCE, will require overhead. I'm also trying to match the output of the other queries.
• ErikE: See update please
• WernerCD: 4 stage process: Original Database > SQL Server 2005 Reporting System > ETL Program > Text File. It's at the 2005 > ETL layer that I'm trying to get to pass "" instead of null, on the generic level, so that I don't have to wrap the reporting system OR the ETL layer in IsNull/Colesce. It's definitely possible, and on the table, but something I'd like to avoid. I hope I'm making sense

Answer 2

Not sure why you think you need to change this at the driver level. Can't you just change your query to the following, which should work fine on both SQL Server and DB2:

SELECT OrderID, COALESCE(CustomerName, '') AS CustomerName
 FROM OrderTable 
 LEFT JOIN CustomerTable 
 ON OrderCustomerID = CustomerID

(Though you may want to properly qualify your column references.)

• WernerCD: If I can toggle something to get "Defaults", it will match outputs of other queries (Where I already get "" instead of "NULL"). It's also going to be much easier at the driver level instead of many queries, some of which have many many columns.