1

I have a MySQL dump file, which is mostly INSERT statements. I want to grep out instances of the string 'media' from a field in a particular table, with +/- ~10 characters of context. The string might appear multiple times in the value of the column. How can I do this?

The source is an extremely large sql dump file (I'll have to severely truncate for brevity):

INSERT...334,'field_media_intro_image','image','image'...);
INSERT...g__vieg__view_mode__media_original attr__format....ategies that are immediately actionable for the task...);

And I would like to see

ield_media_intr
ode__media_orig, re immediately 

Where the first line above is the one instance of the string 'media' in the first matched line, and the second line shows the two instances of the string 'media', from the next matching line. Both show five characters of context at the the beginning and ending of the string. The comma acts as a separator.

  • 2
    Better show example input and desired output – Costas May 14 '15 at 18:27
  • I've added example source and desired output. – user394 May 14 '15 at 18:45
  • 2
    grep -oE '.{5}media.{5}' dump.file – Costas May 14 '15 at 18:52
  • That splits all instances into separate lines, at least on my system (grep 2.6.3) :( But, I can add -n to get the line number at the beginning. – user394 May 14 '15 at 18:57
  • 1
    @Costas: Good answer, but that misses instances of "media" that occur within five characters of the beginning or the end of a line.  To include those, say grep -oE '.{,5}media.{,5}' dump.file. – G-Man May 14 '15 at 19:48
1

GNU grep can grab context by lines (-A LINES for context after, -B LINES for context before, and -C LINES for context both before and after) but it does not have a flag for horizontal context. You can do that with a regex though:

grep -Eo '.{0,10}media.{0,10}'

(-E uses Extended Regular Expressions (ERE), allowing for syntax like .{0,10} (match any character 0–10 times). GNU grep's -o displays only the matched content, one match per line.)

Greediness

Note, this won't be a comprehensive list since some copies of the word "media" may be too close to what is already captured. For example:

$ echo 123 media 12345 media 123456789 media 12 |grep -Eo '.{0,10}media.{0,10}'
123 media 12345 med
234567890 media 123

You get parts of all three instances of "media" but since one of them is partially within ten characters of another, only that part of it was represented.

If have GNU grep compiled with libpcre, you can tell those wildcards to be lazy rather than greedy:

$ echo 123 media 12345 media 123456789 media 12 |grep -Po '.{0,10}?media.{0,10}?'
123 media
 12345 media
234567890 media

The -P flag enables Perl-Compatible Regular Expression (PCRE) evaluation.

The lazy evaluation (also called "non-greedy evaluation") aspires to prevent one match from interfering with another rather than consuming as many of the ten characters as possible, which limits further matches.

If your version of grep doesn't support -P or -o, you can use perl:

$ echo 123 media 123 media 123456789 media 12 |perl -ne \
    'while (/(.{0,10}?media.{0,10}?)/g) { print "$1\n"; }'
123 media
 12345 media
234567890 media

This modifies the regex slightly to include a matching group so we can refer to the matched text later. Otherwise, it's just a loop on each match (the g matches globally rather than just the first time) that then prints the match with a newline.

GNU vs POSIX grep

GNU grep adds a lot of functionality atop the POSIX standard grep. Specific to this answer, -A LINES (lines of context after), -B LINES (lines of context before), -C LINES (lines of context both before and after), -o (show only the match), and -P (use PCRE) are all available in GNU grep but cannot be assumed for other grep implementations. BSD grep supports all of them except -P, but GNU grep is often preferred by BSD users due to GNU's performance optimizations.

Both GNU and BSD grep commands also support --color, which you could use as an alternative to -o. This will end up showing entire lines with the matching text ("media" plus its 0–10 characters of context) colored.

 

A final note: A comment to the question used the syntax .{,5}, which works in grep -E but almost nowhere else (certainly neither grep -P nor perl). It is a bad habit to use that format rather than explicitly including the zero in .{0,5}.

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.