sed: extract text within pattern that occurs an arbiratry number of times in a line

Question

I need to extract a part of string that may appear 1 to n times in each line.

For instance, this would reflect what I need:

This [dbo].[something] is a text containing [dbo].[something_else], then okay?
And then, [dbo].[something] may appear just once.
But why, nothing prevents [dbo].[something] from appearing twice as [dbo].[something] here.
And then can be three times, as [dbo].[something] is [dbo].[anything] but [dbo].[elsewhere] here.
[dbo].[otherthing] depicts another scenario with just one and pattern heading line
Or, also [dbo].[ultra] with an arbitrary amount of [dbo].[references] but ending with [dbo].[pattern]

As you may have noticed, the pattern would be \[dbo\]\.\[[^]]+\]. For instance, from the text above, I would want a result of:

something something_else
something
something something
something anything elsewhere
otherthing
ultra references pattern

Then I can just inline everything (or append to a bash array) and filter duplicates, this shouldn't be an issue. I am just having trouble to figure out how to do this filter in a single sweep.

What I have here, results in extracting just the last match (it is obvious why when you are used to sed's "greedy" approach to pattern matching):

cat dborefs.txt | sed -E "s/(.*\[dbo\]\.\[([^]]+)\].*)*/\2/g"
something_else
something
something
elsewhere
otherthing
pattern

I could extract, then replace the patterns so that they no longer match, then extract again until I get no more matches, but that sounds just too cumbersome, all bash overhead considered; it would be best to be able to extract everything in a single call to sed. I feel this should be possible, just can't easily figure out how. Thinking this may be useful for others, I felt like sharing the matter here could prove fruitful for the community.

Answer 1

To get a newline-delimited list of marked up strings:

$ grep -o '\[dbo\]\.\[[^]]*\]' file | cut -d . -f 2 | tr -d '[]'
something
something_else
something
something
something
something
anything
elsewhere
otherthing
ultra
references
pattern

The first grep only produces lines with [dbo].[word]. The cut gives us the [word] bit and the tr removes the [ and] from this.

To get the marked up strings grouped by the line they occur on:

$ sed -e 's/\][^.[]*\[/] [/g' -e 's/^[^[]*//' -e 's/[^]]*$//' -e 's/\[dbo\]\.\[\([^]]*\)\]/\1/g' file
something something_else
something
something something
something anything elsewhere
otherthing
ultra references pattern

The four substitutions used here are

1. Delete everything between ] and [ that isn't a dot or a [ (actually, replace with a space; these are the spaces in the final output).
2. Delete everything up to the first [.
3. Delete everything after the last ].
4. Extract the marked up words in what remains.

Answer 2

For now, what I could have that is (hopefully) much better than repeatedly calling sed was to "chain" replacements with placeholders that hopefully won't appear in the files.

cat dborefs.txt | sed -E "
 s/\[dbo\]\.\[([^]]+)\]/_-\1-_/g;
 s/(^|-_)([^_]+|_[^-])*(\$|_-)/ /g;
 s/(^ +| +\$)//g"

In other words:

• first I'm getting all [dbo].[<extract>] and replacing with _-<extract>-_;
• then replace any text before the first _-, between -_ and _-, and after the last -_ by a single whitespace character;
• and then clean up whitespace characters in the begin and end of each line.

This gives the desired result, and I may be able to join it all in an array then filter with sort for unique entries. But I still think there should be a better way without chained sed commands.

Answer 3

You could do this more easily in Perl, using a hash (associative array) to uniquify the matches:

$ perl -nE 'while ($_ =~ /\[dbo\]\.\[(.*?)\]/g) {$h{$1}++} }{ for $k (keys %h) {say $k}' dborefs.txt 
otherthing
anything
elsewhere
something
pattern
something_else
ultra
references

A similar approach is possible in GNU Awk, by repeated application of the match function:

$ gawk '{
    while (match($0,/\[dbo\]\.\[([^]]+)\]/,a)) {h[a[1]]++; $0 = substr($0,RSTART+RLENGTH)}
  } 
  END{
    for (k in h) print k
  }' dborefs.txt 
references
elsewhere
something
something_else
pattern
otherthing
anything
ultra

With other Awk implementations whose match function doesn't provide the array of capture groups, you'd need to trim the match:

while (match($0,/\[dbo\]\.\[([^]]+)\]/)) {h[substr($0,RSTART+7,RLENGTH-8)]++; $0 = substr($0,RSTART+RLENGTH)}

Answer 4

Employing gnu sed in extended regex mode (to save escaping with backslashes to some extent):

sed -re '
  /\n/q;G
  :a;s/\[dbo]\.\[([^]]+)](.*\n\1(\n|$))/\2/;ta
  :b;s/\[dbo]\.\[([^]]+)](.*)/\2\n\1/;tb
  s/^[^\n]*\n//;h
  $\!d;g;D
' dborefs.txt


awk -F'[][]' '
NF>2{
  for (i=1; i<=NF-2; i++)
    if ( $(i) $(i+1) == "dbo." )
      a[$(i+2)]
 }
 END { for (i in a) print i } 
' dborefs.txt

Output;

something
something_else
anything
elsewhere
otherthing
ultra
references
pattern

perl -lne '
  $h{$1}++ while /\[dbo]\.\[([^]]+)]/g;
  }{print for keys %h;
' dborefs.txt

Answer 5

Yet another method, tthis time employing multiple utilities. The sed portion of the pipeline extracts the pattersns whilst the awk portion uniquifies it also at the same time preserving the order in which they were first seen.

sed -Ee '
  /\n/{P;D;}
  s/\[dbo]\.\[([^]]+)]/\n\1\n/;D
' dborefs.txt | awk '!a[$0]++'

Answer 6

Another fun one with recursion. However, it's not a one line sed command...

$ sed -e ':loop;
          s/[^|\[]*\[dbo\]\.\[\([a-z_]*\)\][^[]*/\1|/;
          t loop;
          s/|$//;
          s/|/ /g' testfile
something something_else  
something  
something something  
something anything elsewhere  
otherthing  
ultra references pattern

• s/...: replace the beginning of the line until [dbo].[myname] by myname|
• t loop: iterate if a substitution was made
• s/|...: get rid of |, replace with space or end of line

Answer 7

You were on the right track, using your \[dbo\]\.\[[^]]+\]

• adding a capture parenthesis: \[dbo\]\.\[([^]]+)\]

• adding an initial capture [^[]* ==> [^[]*\[dbo\]\.\[([^]]+)\]

• surrounding it in a substitution group s/ ... /\1]/g

• Removing the last ] and what follows: s/\][^]]*$//

• and converting all remaining ] to spaces: s/\]/ /g

Will get you there:

sed -Ee 's/[^[]*\[dbo\]\.\[([^]]+)\]/\1]/g' \
     -e 's/\][^]]*$//' \
     -e 's/\]/ /g' file

will yield:

something something_else
something
something something
something anything elsewhere
otherthing
ultra references pattern

Answer 8

I was not very clear what your expected output was. Sometimes you talk of filtering dups, other times one per line.

This no chaining, all sed solution uses the most secure placeholder rather than - _

sed -Ee '
  s/\[dbo]\.\[([^]]+)]/\n\1\n\n/g
  s/(^|\n\n)[^\n]*//g
  y/\n/ /
' file