OK, here it is.

I am joining two tables (A + B)...as part of my where clause I wish to equate one char column from A table to one char column from table B...this has to be done via the "LIKE" clause, here is why...

The char column in table B may or may not have a value in it. When no value is present the column will be set to NULL....

The idea in the SQL is to resolve the LIKE clause to either a valid and specific value from table B's column or wildcard the LIKE clause (% - meaning get everything) when table B's column is NULL

I am running a COALESCE function on the column from table B and want it to return "%" (wildcard) if the value is NULL or the value itself if column is not NULL.

Another words, if the column in table B has a valid value, the where clause should look like this:

WHERE A.col1 LIKE 'valid value from B.col1'

if no valid value is present (NULL) then the where clause should look like this:

WHERE A.col1 LIKE '%'

I have tried all kinds of flavors, but cannot get any of them to work syntactically. Here are some of the things I have tried:

...WHERE a.col1 LIKE COALESCE(b.col1, '%');
...WHERE a.col1 LIKE COALESCE(b.col1, ''%'');
...WHERE a.col1 LIKE ''' || COALESCE(b.col1, '%') || ''';


Any input or feedback is appreciated.

Thanks,
Matt.

I think "LIKE" only takes a literal string. Have you tried this:

... WHERE A.COL1=B.COL1 OR B.COL1 IS NULL....

I think this should do what you want.

Hi

If the LIKE-predicate starts at position 1, you can use the SUBSTR-function instead and if B.col1 is NULL you should use a left outer join. Have you tried that?

I belive this may work....another person suggested the exact thing this morning. Thank you! Going to test now...Matt.

Matt ,

if the string matching string can occur anywhere then you can use the LOCATE function

Eg :

From the SAMPLE Employee table

C:\Documents and Settings\Sathyaram>db2 "select firstnme from employee where locate('SA',firstnme) > 0"

FIRSTNME
------------
SALLY
MASATOSHI
SALVATORE

3 record(s) selected.

Works like a charm. Thanks, Matt.