DB2 efficient select query with like operator for many values (~200)

Question

I have written the following query:

SELECT TBSPACE FROM SYSCAT.TABLES WHERE TYPE='T' AND (TABNAME LIKE '%_ABS_%' OR TABNAME LIKE '%_ACCT_%')

This gives me a certain amount of results. Now the problem is that I have multiple TABNAME to select using the LIKE operator (~200). Is there an efficient way to write the query for the 200 values without repeating the TABNAME LIKE part (because there are 200 such values which would result in a really huge query) ?

(If it helps, I have stored all required TABNAME values in a table TS to retrieve from)

Possible duplicate of [How to use LIKE with IN in DB2?](https://stackoverflow.com/questions/11838919/how-to-use-like-with-in-in-db2) — vahdet, Feb 15 '19 at 09:17
Hi @vahdet, I am less interested in using the `LIKE OR` part because there are 200 such values. This is why I want to know whether there is another way to do it — Sparker0i, Feb 15 '19 at 09:20

Paul Vernon · Accepted Answer · 2019-02-15T10:20:33.550

1

If you are just looking for substrings, you could use LOCATE. E.g.

WITH SS(S) AS (
    VALUES
        ('_ABS_')
    ,   ('_ACCT_')
)
SELECT DISTINCT
    TABNAME
FROM
    SYSCAT.TABLES, SS
WHERE
    TYPE='T' 
AND LOCATE(S,TABNAME) > 0

or if your substrings are in table CREATE TABLE TS(S VARCHAR(64))

SELECT DISTINCT
    TABNAME
FROM
    SYSCAT.TABLES, TS
WHERE
    TYPE='T' 
AND LOCATE(S,TABNAME) > 0

edited Feb 15 '19 at 10:20

answered Feb 15 '19 at 09:28

Paul Vernon

3,818
1
10
23

The problem is, like ABS, ACCT, there are 200 such values – Sparker0i Feb 15 '19 at 10:03
Cant it be possible that these values be retrieved from a table (I have stored it in a table) and then substituted in the `VALUES` ? – Sparker0i Feb 15 '19 at 10:04
sure, just replace my SS with your table – Paul Vernon Feb 15 '19 at 10:18

score 0 · Answer 2 · answered Feb 15 '19 at 09:24

0

You could try REGEXP_LIKE. E.g.

SELECT DISTINCT
    TABNAME
FROM
    SYSCAT.TABLES
WHERE
    TYPE='T' 
AND REGEXP_LIKE(TABNAME,'.*_((ABS)|(ACCT))_.*')

answered Feb 15 '19 at 09:24

Paul Vernon

3,818
1
10
23

score 0 · Answer 3 · answered Feb 15 '19 at 11:07

Just in case.
Note, that the '_' character has special meaning in a pattern-expression of the LIKE predicate:

The underscore character (_) represents any single character.

The percent sign (%) represents a string of zero or more characters.

Any other character represents itself.

So, if you really need to find _ABS_ substring, you should use something like below.
You get both rows in the result, if you use the commented out pattern instead, which may not be desired.

with 
  pattern (str) as (values
  '%\_ABS\_%'
--'%_ABS_%'
)
, tables (tabname) as (values
  'A*ABS*A'
, 'A_ABS_A'
)
select tabname
from tables t
where exists (
  select 1
  from pattern p
  where t.tabname like p.str escape '\'
);

DB2 efficient select query with like operator for many values (~200)

3 Answers3