I need to extract all host variables from a SQL statement that are part of a concatenation. Example input:

select * from table where :first-var || :second-var

From this I need to extract 'first-var' and 'second-var'.

Matching one or the other can be done using:

grep -o -E ':\S+\s+\|\|'

matches ':first-var ||' and

grep  -o -E '\|\|\s+:\S+'

matches '|| :second-var'

However when I combine these two expressions in an alternation pattern only one result is returned:

grep -o -E '\|\|\s+:\S+|:\S+\s+\|\|'

Splitting the command up in multiple patterns also matches only one result:

grep -o -E -e '\|\| :second-var' -e ':first-var \|\|'

I suspect that the pipe symbols are "used up" after the first match because the following does return both results:

grep -o -E -e '\| :second-var' -e ':first-var \|'

How can I get all the matches? Note that the concatenation symbols do not need to be part of the output, I'm only interested in 'first-var' and 'second-var' in this example.

  • 1
    You want to match all :foo, but only if they're either preceded or succeeded by ||? Or you just want to match :foo and :bar in :foo || :bar? Commented Sep 4, 2017 at 15:08
  • @SatōKatsura I want to match all :foo if they're preceded or succeeded by ||.
    – Bram
    Commented Sep 4, 2017 at 15:45

3 Answers 3


The problem is that after finding :first-var ||, there's no || :second-var left in the remaining text. grep -o can only print portions of the lines that don't overlap.

You could do:

$ perl -lne 'print for /:\S+\s+\|\|/g, /\|\|\s+:\S+/g' file
:first-var ||
|| :second-var

(with all the :var ||s printed before the || :vars).

Or if you want only the :var part, with GNU grep with PCRE support:

$ grep -Po ':\S+(?=\s+\|\|)|\|\|\s+\K:\S+' file

Same as:

perl -lne 'print for /:\S+(?=\s+\|\|)|\|\|\s+\K:\S+/g'

Now, if what you want is extract the :foo and :bar in a line that is always shaped like anything :foo || :bar, you could do it standardly with:

s='[[:space:]]\{1,\}' S='[^[:space:]]\{1,\}'
sed -n "/\(:$S\}\)$s\{1,\}||$s\(:$S\).*/{
  • 2
    A short explanation: (?=...) is a lookahead assertion. The corresponding lookbehind (?<=\|\|\s+) doesn't work because Perl / PCRE only accepts lookbehinds with a fixed length. The usual way to overcome this limitation is to use \K instead, hence the solution above. Commented Sep 4, 2017 at 16:07
  • I don't have perl on this system but the PCRE solution is twice as fast as the double pass solution I had in place. Thanks
    – Bram
    Commented Sep 5, 2017 at 12:11
perl -nE 'say "$1\n$2" if /(:\S+)\s*\|\|\s*(:\S+)/'

Rather than trying to craft a complicated regular expression, just do two passes over the file:

grep -o '|| *[^ ]*' file
grep -o '[^ ]* *||' file

Or, combine with awk:

grep -o '[^ ]* *|| *[^ ]*' file | awk -F' *\\|\\| *' '{ print $1; print $2 }'

Given the example line

select * from table where :first-var || :second-var

that would produce

  • Thanks, I'll certainly try the awk bit. But the problem with multiple passes is that it's terrible for performance. Especially if the number of input files grows. It is the solution I've used now but I'm looking for a better solution.
    – Bram
    Commented Sep 4, 2017 at 21:22
  • @Bram The solution with awk will only do a second pass over the lines that contains concatenations (and only the selected bits of them), not the whole file.
    – Kusalananda
    Commented Sep 4, 2017 at 21:24
  • this solution doesn't give me all the expected results but I realize that's because I simplified my example in the question too much. Stephane's solution gives me everything I need. Thanks for looking at this.
    – Bram
    Commented Sep 5, 2017 at 12:23

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .