0

I have a long, long log file of SQL commands, and I'm interesting in grep'ing just the name of the tables. For example:

SELECT * FROM table_02323
SELECT * FROM table_231
SELECT * FROM table_1

I want to grep so that the result returns

table_02323
table_231
table_1

The closest thing I've gotten is grep -o 'table.....' this.log but obviously the suffix will have different lengths. Is there a regex expression to output without being constrained by a fix length as is the case w/ using periods?

  • 1
    I assume you only want table names from "select" statements?? And will the table name always be on the same line as the select? Do all the table names start with "table_"? – Jeff Schaller Sep 1 '17 at 18:58
  • egrep -io '\<from[ ]+[^ ]+' | sort -u, then you can use sed or grep again to remove the FROMs. But split lines would fool this, you'd need a real SQL parser to get around that. – Satō Katsura Sep 1 '17 at 18:58
4

This will match "table_" following by 0 or more ASCII digits:

grep -o 'table_[0-9]*' this.log
0

Use GNU grep like below:

grep -oiP 'FROM\s*\K\w+' infile

Non-GNU:

grep -ioP 'FROM[ ]*\K[A-Z_0-9]+' infile

This will ensure you are just getting table name.

Test:

grep -ioP 'FROM\s*\K\w+' <<<"SELECT * FROM table_02323 where 1=1"
table_02323
  • Yes, updated answer – αғsнιη Sep 1 '17 at 19:03
0

From man grep:

Repetition
       A regular expression may be followed by one of several repetition operators:
       ...
       *      The preceding item will be matched zero or more times.

So you want to use something like

$ grep -o 'table\w*'

where \w is any non-whitespace character, repeated zero or more times.

0

Just another approach, using lookbehind:

$ grep -P -o -e '(?<=^SELECT \* FROM )(.*$)' < 'file'
table_02323
table_231
table_1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.