Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2013
    Posts
    46

    Unanswered: Select table names where like '%_%' doesn't work

    Hi,

    I am trying to use this query:

    select tabschema,tabname,colname from syscat.columns where colname like '%HEX%' and tabschema='REP_SA' and tabname like '%_%'

    ..But the condition tabname like '%_%' doesn't seem to work because it still returns names without the "_".

    Please advise!

    Thanks in advance.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    The _ is a wildcard for a single character (similar to the ? when using the dir command in Windows). You need to tell the database that it should not be treated as

    Code:
    select tabschema,tabname,colname 
    from syscat.columns 
    where colname like '%HEX%' 
      and tabschema = 'REP_SA' 
      and tabname like '%^_%' ESCAPE '^'
    The ESCAPE '^' part tells the database that the character following the ^ should not be treated as a wildcard.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Sep 2013
    Posts
    46
    Thanks!!! Works like a charm.

  4. #4
    Join Date
    Sep 2013
    Posts
    46
    Shammat,

    Please help me in another sql if possible:

    I am trying to create foreign keys on a lot of tables at once using a script to generate all of the alter statements. Don't really feel like type all statements manually.

    TABLE - PARENT TABLE NAME
    <TABLE>_X_Y_Z - child table name format (<TABLE> means parent table name)

    Here is a sample of how it should look:
    ALTER TABLE SCHEMA.TABLE_X_Y_Z
    ADD CONSTRAINT FK_TABLE_X_Y_Z FOREIGN KEY
    (TABLE_HEX)
    REFERENCES REP_SA.TABLE
    (TABLE_HEX);

    So far I have this….
    select 'alter table '||rtrim(tabschema)|| '.' ||rtrim(c.tabname)|| '
    ADD CONSTRAINT FK_'||rtrim(c.tabname)|| ' FOREIGN KEY
    ('||rtrim(p.tabname)||'_HEX)
    REFERENCES '||rtrim(tabschema)||'.'||rtrim(p.tabname)||

    ..and then I thought maybe I don’t need to join to select child table name and parent table name because: all child table names are this format: <PARENT_TABLE_NAME>_XXXXX i.e. parent name is <PARENT> and child table name is <<PARENT>_X_Y_Z> (as u can see the string before the first _ is the same name as parent table—this will always be the case for all tables. How do I select the name <PARENT> from child table name? This way i can use whatever function possible to select the parent table name to refer to as well as the column name TABLE_HEX.

    I hope this makes sense..

  5. #5
    Join Date
    Sep 2013
    Posts
    46

  6. #6
    Join Date
    Sep 2013
    Posts
    46
    instead of SUBSTR, i used substr2 - and then it worked!

Posting Permissions

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