Results 1 to 6 of 6

Thread: SQL Help

  1. #1
    Join Date
    Dec 2006
    Posts
    3

    Unanswered: SQL Help

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    May 2006
    Posts
    82
    How about this:

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

  4. #4
    Join Date
    Dec 2005
    Posts
    273
    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.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Dec 2005
    Posts
    273
    also possible ( but with poor performance )

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •