awk command to extract particular string based on multiple pattern string

Question

I have following string in a file called ddl.txt

CREATE SET TABLE DB.TABLE1 ,NO FALLBACK ,
     NO BEFORE JOURNAL (
      key DECIMAL(18,0);

drop table DB.dummy;
create table DB.dummy (id varchar(10));

I need to extract table names in above file as follows which contain key words 'TABLE' & 'CREATE' but in different pattern, (Expected Output)

TABLE1
dummy

what i tried is,

a=`cat ddl.txt`
a=`echo "$a" | tr [a-z] [A-Z]`

echo "$a" | awk -v RS=, '/TABLE/&&/CREATE/{print $NF}' | awk -F'.' '{print $2}'

This is returning only

TABLE1

I need the other table name dummy which is in different pattern search.

Note: Always we should check the pattern is matching with keywords TABLE & CREATE as i used in above query; also DB is dynamic ... it is not always DB

jaypal singh · Accepted Answer · 2014-03-04 15:32:20Z

1

Here is another way with GNU awk:

awk '
BEGIN{IGNORECASE=1}
/create/&&/table/{for(i=1;i<=NF;i++) if($i~/[.]/){split($i,tmp,/\./); print tmp[2]}}' ddl.txt

Output:

TABLE1
dummy

answered Mar 4, 2014 at 15:32

jaypal singh

77.6k24 gold badges108 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

logan Over a year ago

but in may case db should be used as key word as it may vary. we can use . but need to take if somewhere dot is coming ... please reframe the command accordingly

Fredrik Pihl · Accepted Answer · 2014-03-04 15:59:02Z

1

Will this help?

$ cat t.awk
/CREATE.*TABLE/{table1=$5}
/create table/{table2=$4}
END{
    print table1
    print table2
}

$ awk -F'[ .]' -f t.awk input.txt
TABLE1
dummy

Update

Assume the folllowing input:

CREATE SET TABLE DB.TABLE1 ,NO FALLBACK ,
     NO BEFORE JOURNAL (
      key DECIMAL(18,0);

drop table DB.dummy;
create table DB.dummy (id varchar(10));

CREATE SET TABLE BD.TABLE2 ,NO FALLBACK ,
     NO BEFORE JOURNAL (
      key DECIMAL(18,0);

drop table BD.dummier;
create table BD.dummier (id varchar(10));

awk:

/CREATE.*TABLE/{
    t1[n++] = $5
}
/create table/{
    t2[k++] = $4
}
END{
    for (i=0; i<=n; i++) {
        print t1[i], t2[i]
    }
}

Output:

$ awk -F'[ .]' -f t.awk input.txt
TABLE1 dummy
TABLE2 dummier

edited Mar 4, 2014 at 15:59

answered Mar 4, 2014 at 15:20

Fredrik Pihl

45.9k7 gold badges89 silver badges133 bronze badges

3 Comments

logan Over a year ago

I may have n number of tables which is not fixed. can you give me a dynamic solution ?

Fredrik Pihl Over a year ago

yes if you update your question so that I understand what you need :-)

logan Over a year ago

I updated question.. also DB is dynamic ... it is not always DB

jrjc · Accepted Answer · 2014-03-04 16:02:41Z

1

If I get what you meant, I would do this :

$ cat a
CREATE SET TABLE DB.TABLE1 ,NO FALLBACK ,
     NO BEFORE JOURNAL (
      key DECIMAL(18,0);

drop table DB.dummy;
create table DB.dummy (id varchar(10));

$ kw="DB." # you can set kw according to what happens previously
$ awk -F"$kw" '(($0~/table/ || $0~/TABLE/) && ($0~/create/ || $0~/CREATE/)){print $2}' a | awk '{print $1}'

 TABLE1
 dummy

I assume the name are always after "DB." pattern

edited Mar 4, 2014 at 16:02

answered Mar 4, 2014 at 15:21

jrjc

22.1k10 gold badges67 silver badges79 bronze badges

6 Comments

logan Over a year ago

but in may case db should be used as key word as it may vary. we can use . but need to take if somewhere dot is coming ... please reframe the command accordingly

jrjc Over a year ago

If you know the keyword, you can pass it to awk instead of "DB."

logan Over a year ago

sorry what i meant is db should not be used as key

jrjc Over a year ago

why not? After the edit you can easily modify DB with the keyword you want.

logan Over a year ago

DB is dynamic word; only CREATE and TABLE are static

|

Collectives™ on Stack Overflow

awk command to extract particular string based on multiple pattern string

3 Answers 3

1 Comment

3 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related