Hi folks,

Can anyone shed light on whether the following is possible using a database query in DB2.

My table contains a column named "Pattern" which contains wildcards like '%AE%', '%_ING' etc....

Now I want some valid equivalent query for this one
select PATTERN from MYTABLE WHERE 'myname' Like PATTERN.

Though the above query is stupid - I see you get the point. I want the COLUMN data to be used as the Wildcard pattern.

Could anyone tell me how to do this ????.

Hi,

no can do - afaik DB2 expects a string constant as target for the like clause. I believe DB2 is standard conform in this regard.

Exact knowledge of the pattern enables database engines to come up front with a decent access plan. If you were to hide the pattern in tables the database engines would need to settle for the easiest access plan perceivable - table scans.

The only way out is to read the patterns into your applications, and have your application compose strings for either dynamic SQL or prepare/execute/fetch.

If you know your patterns either start or end with "%", you can come around the limitations and use the inbuilt functions RIGHT, LEFT and LOCATE. For this to work, you would still need to discard of the wildcard characters, that is you would need to know if to do RIGHT or LEFT or whatever, possibly by doing a LOCATE on % and _ and then wrap it up in a CASE expression and then do a REPLACE on % and _. This can quickly become complicated, if you want to do it by SQL alone.

Johann