I want to "match" by two columns. So something like:

SELECT * FROM tablea A, tableb B WHERE B.msglist CONTAINS A.msg;



A.msg looks like
MSG1
MSG2
MSG3
etc

and B.msglist looks like
(MSG1)&&(MSG3)&&(MSG9)
(MSG1)&&(MSG2)&&(MSG6)
(MSG8)&&(MSG7)&&(MSG3)
etc


is there any way to do that?

String handling isn't very consistent between different versions of SQL. The exact syntax will depend on what SQL engine you are using, but it is definitely possible.

If you can settle for a "loose match" that might be confused by special characters like _ and %, you could use concatenate percent signs at the begining and end of your "search for" column, and use that as the LIKE pattern. This is reasonably portable, but it still depends on the string concatenation syntax (which varies from one SQL engine to another).

-PatP

Thanks. I'm on DB2

So something like:

SELECT * FROM tablea A, tableb B WHERE B.msglist LIKE % + A.msg + %;

I dont even know how to concatenate strings in db2 =\

I'm moving this thread to the DB2 forum, since you'll get a lot more relevant comments there than in the "pure" SQL forum.

I'd use:-PatP

I'm afraid, you cannot use a table column at the right side of a LIKE. ( at least in DB2 for z/OS you can't )

but a

... WHERE LOCATE(A.msg , b.msglist ) > 0

should work.
LOCATE returns the position of parameter1 in parameter2 if found, and zero if parameter2 does not contain parameter1

Really? I thought expressions as the LIKE pattern were supported in DB2 7.2 and later across all platforms. Gee, I learned something, so I can go home now!

-PatP

LIKE itself is supported in DB2 z/OS, of course.

WHERE column-name LIKE expression

but expression must be:
a special register or
a host-variable or
a string-constant or
a result of a function based on the above.

expression must not be (or contain) a column-name