SQL Help

Db2Rookie · #1 (**permalink**) 01-27-07, 14:40

I also have tables like I_TABLE_A, I_TABLE_B in a database. I need a SQL to get the list of tables starting with "I_"

Thanks a million in advance

Marcus_A · #2 (**permalink**) 01-27-07, 15:29

I know you probably are looking for the escape character, but this might also work:

Code:

select tabschema, tabname
from syscat.tables
where type = 'T'
and tabname between 'I_         ' and 'I_ZZZZZZZZZ'

If you are working on DB2 for z/OS (EBCDIC) then you might have to substitute 9's for the Z's

vini_srcna · #3 (**permalink**) 01-29-07, 01:23

How about this:

SELECT CREATOR,NAME FROM SYSIBM.SYSTABLES
WHERE TYPE = 'T' AND NAME LIKE 'I_%'

umayer · #4 (**permalink**) 01-29-07, 08:33

Quote:

Originally Posted by vini_srcna

SELECT CREATOR,NAME FROM SYSIBM.SYSTABLES
WHERE TYPE = 'T' AND NAME LIKE 'I_%'

This query doesn't work, as the underscore is a *joker* for any single character. You have to escape it.

... WHERE TYPE = 'T' AND NAME LIKE 'I+_%' ESCAPE '+'

should work.

stolze · #5 (**permalink**) 01-29-07, 09:53

Yet another approach:

Code:

SELECT ...
FROM  ...
WHERE SUBSTR(tabname, 1, 2) = 'I_'

Notes:

this will not make use of an index unless you have a generated column based on the above SUBSTR expression
it will fail with an error if you have tables whose names are shorter than two characters; in that case, you will need a CASE expression - expressions can be evaluated in arbitrary order so that adding a simple "AND LENGTH(tabname) >= 2" will not work

umayer · #6 (**permalink**) 01-30-07, 03:09

also possible ( but with poor performance )

... WHERE LOCATE('I_' , NAME ) = 1

Internet Services

Web Development

Digital Marketing

Consumer Tech