PDA

View Full Version : Solved: Using SQL to translate Nulls into Empty Strings



Dr.K
12-12-2007, 10:08 AM
My SQL skills are kinda weak... please be kind.

Currently, I use "null scrubber" function to Null fields into empty strings, becuase VBA will choke if its expecting a string and it gets a Null.

Function Null2Empty(strField) As String

If IsNull(strField) = True Then
Null2Empty = Empty
Else
Null2Empty = strField
End If

End Function

However, I pretty sure that there are ways of translating Nulls into Empty Strings right in the SQL statement, I just don't know how.

Any advice is apppreciated.

XLGibbs
12-12-2007, 03:40 PM
SQL has an isNull function..

IsNull(ColumnName,value) as ColumnName

so the value would replace anything that would be null.

A better option would be to have the source data NOT accept nulls in that column and make the default value an empty string.

Dr.K
12-13-2007, 06:14 AM
Ah thank you, thats a good start!


A better option would be to have the source data NOT accept nulls in that column and make the default value an empty string.

Well, I'm pulling from two DBs: I have no control over whats in the Data Mart, so I'll have to use the SQL function you showed me.

As for the Access DB, how do I set that up?
Required = true
Allow zero length = true

Will that do it?

Thanks again.

Dr.K
12-13-2007, 04:21 PM
Actually, IsNull doesn't work on DB2. Since my Data Mart is on a DB2 server, I had to use COALESCE instead of IsNull.

I googled around and found a DB2 SQL reference on the IBM website, this cleared up the problem for me. This is what my usage looks like:


COALESCE(MSE_TCKR_SYMB, '') AS MSE_TCKR_SYMB


Thanks for the guidance!