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? – Satō Katsura Sep 4 '17 at 15:08
  • @SatōKatsura I want to match all :foo if they're preceded or succeeded by ||. – Bram Sep 4 '17 at 15:45

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\).*/{
| improve this answer | |
  • 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. – Satō Katsura Sep 4 '17 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 Sep 5 '17 at 12:11
perl -nE 'say "$1\n$2" if /(:\S+)\s*\|\|\s*(:\S+)/'
| improve this answer | |

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

| improve this answer | |
  • 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 Sep 4 '17 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 Sep 4 '17 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 Sep 5 '17 at 12:23

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.