How to extract text between multiple parentheses on a single line in MySQL dump file

Question

I have a MySQL dump file containing several tables. I already have a SED command to extract one single table.

Now I need to know how to extract only the records associated with a specific unit_id. The format is as follows:

INSERT INTO tablename (1,999,'sometext'), (2,999,'othertext'),(3,997,'text here'),(4,123,'a string'), ...

Where 999 is the unit id (there can be multiple records for a single unit id)

My desired result is:

999,'sometext'
999,'othertext'
...

... for every entry where 999 (or any specific number I choose) appears in the second column.

I tried using sed to select the values between parentheses, like this:

sed -n 's/\((.*,999,.*)\)/\1/p' < result.sql

Where 999 is the id I'm searching for.

but it returns nothing.

Answer 1

Sed cannot output the multiple matching results in the same line at a time. As a workaround we can split the input line at an appropriate punctuation into multiple lines as a preprocessing.

Would you try the following:

sed 's/), *(/)\'$'\n''(/g' result.sql | sed -n 's/.*,\(999,.*\)).*/\1/p'

Output:

999,'sometext'
999,'othertext'

• The first sed command s/), *(/)\'$'\n''(/g' finds a comma between a closing paren and an opening paren then replace it with a newline. (It assumes you are running bash).
• The next sed command 's/.*,\(999,.*\)).*/\1/p'extracts your desired values.

Alternatively grep with -P option (perl-compatible regex support) will also work.

grep -Po '(?<=,)999,.*?(?=\))' result.sql

• The pattern (?<=,) is a zero-width lookbehind assertion which matches a leading comma without including it in the result.
• The pattern .*? avoids the greedy match.
• The pattern (?=\)) is a zero-width lookahead assertion which matches a trailing right paren without including it in the result.

Answer 2

With GNU awk for multi-char RS and RT:

$ awk -v RS='[(][^)]+)' -F'[(),]' -v OFS=, '{$0=RT} $3==999{print $3, $4}' file
999,'sometext'
999,'othertext'