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

    Unanswered: selecting a string before the first occurrence of a character

    Hi all,

    I posted the following question as a reply to a question i had earlier but figure that was not the right way. So here's a new thread for the particular sql question:

    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: 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.

    Please advise.

  2. #2
    Join Date
    Sep 2013
    Posts
    46

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

Tags for this Thread

Posting Permissions

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