Unanswered: selecting a string before the first occurrence of a character
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
So far I have this….
select 'alter table '||rtrim(tabschema)|| '.' ||rtrim(c.tabname)|| '
ADD CONSTRAINT FK_'||rtrim(c.tabname)|| ' FOREIGN KEY
..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.