2009-07-30

Transact SQL for including concatenated text fields in WHERE clause

I am working with a database on an SQL Server that carries forward a legacy table structure where the description field for a Purchase Order line item is spread across multiple text columns in the table.  In other words, instead of having one 250 character text field for the item description it has a total of 7 text fields of (I think) about 25 characters each.  I have a web application where users can search for purchase orders using the description fields.  My original solution was to check for the user's search term across each of the seven text fields using OR clauses.  However, this meant that a user's search term would not be found if it happened to straddle the border between two of the seven fields.

To solve this problem I tried concatenating all of the description fields and then testing the concatenated string using LIKE, but that resulted in some POs not being inlcuded in the results when they should have been.  I finally puzzled out that what was happening was that database treated empty description fields as null and Transact SQL gives a null value for the whole concatenation if you try to concatenate a number of valid string values with one null value.  In other words if you concatenate Field1 + Field2 + Field3, and the first two fields have text in them, but Field3 does not, then Transact SQL will give you a null result.  So the solution was to use ISNULL to convert null description fields to zero length strings when concatenating them for the WHERE clause, so that one null value doesn't ruin the whole concatenation, like so:

WHERE Field_1 + ISNULL(Field_2,"") + ISNULL(Field_3,"") + ISNULL(Field_3,"") LIKE %search-term%