0

I'm using bash shell attempting to do a search and replace. I have a file of lines that look like

...
INSERT INTO currency (name, code, symbol) VALUES ('Baht', 'THB', '฿');
...

and I want to convert each line to be

currency = Currency.find_by_iso('THB') || Currency.new(:code => 'THB')

I'me extracting the second argument from the INSERT SQL command as you can see. I thought I could do this

perl -w -pe "s/INSERT INTO currency (name, code, symbol) VALUES ('(.*?)', '(.*?)', '(.*?)');/currency = Currency.find_by_iso(\$&) || Currency.new(:code => '\$&')/" currencies.rb

but nothing is happening -- that is, the output of the replacement is leaving the lines unchanged. How do I capture the second value from my SQL statement and create a new line out of it?

  • Escape your literal parentheses. – Wildcard Aug 29 '17 at 22:32
1

the output of the replacement is leaving the lines unchanged

This indicates that your regex is not matching the input, so lets take a step back and see if we can get a minimal regex that works:

perl -w -pe "s/INSERT INTO currency (name, code, symbol) VALUES ('(.*?)', '(.*?)', '(.*?)');//" currencies.rb
> INSERT INTO currency (name, code, symbol) VALUES ('Baht', 'THB', '?');

Simply removed the replacement and unsurprisingly there is no difference to what you first got, it is not matching the input.

Now the last part of your regex ('(.*?)', '(.*?)', '(.*?)') contains allot of chars that have special meaning in regex so lets remove them and see if that works:

perl -w -pe "s/INSERT INTO currency (name, code, symbol) VALUES .*;//" currencies.rb
> INSERT INTO currency (name, code, symbol) VALUES ('Baht', 'THB', '?');

Still not matching, now the only special chars are the () which should probably be escaped:

perl -w -pe "s/INSERT INTO currency \(name, code, symbol\) VALUES .*;//" currencies.rb
> 

Yup that matches - our input was matched and deleted, so lets add in the end bit again, this time also escaping the other ()s as well:

perl -w -pe "s/INSERT INTO currency \(name, code, symbol\) VALUES \('(.*?)', '(.*?)', '(.*?)'\);//" currencies.rb
> 

Still matches, so lets add in the replacement again:

perl -w -pe "s/INSERT INTO currency \(name, code, symbol\) VALUES \('(.*?)', '(.*?)', '(.*?)'\);/currency = Currency.find_by_iso(\$&) || Currency.new(:code => '\$&')/" currencies.rb
> currency = Currency.find_by_iso(INSERT INTO currency (name, code, symbol) VALUES ('Baht', 'THB', '?');) || Currency.new(:code => 'INSERT INTO currency (name, code, symbol) VALUES ('Baht', 'THB', '?');')

Hmm, seems to be matches the wrong part. This is because & is replaced by the entire matched expression rather than a single subgroup instead you want $1, $2 etc:

perl -w -pe "s/INSERT INTO currency \(name, code, symbol\) VALUES \('(.*?)', '(.*?)', '(.*?)'\);/currency = Currency.find_by_iso(\$2) || Currency.new(:code => '\$2')/" currencies.rb
> currency = Currency.find_by_iso(THB) || Currency.new(:code => 'THB')

Almost there, missing some quotes - we also do not require the other two subgroup matches so lets remove them:

perl -w -pe "s/INSERT INTO currency \(name, code, symbol\) VALUES \('.*?', '(.*?)', '.*?'\);/currency = Currency.find_by_iso('\$1') || Currency.new(:code => '\$1')/" currencies.rb 
> currency = Currency.find_by_iso('THB') || Currency.new(:code => 'THB')

There we go, exactly what we wanted.

When faced with complex regex that does not seem to work it is normally a problem with some of the special chars, it varies from languages to languages and tool to tool - sometimes things need to be escaped sometimes they do not. It always helps to start by stripping out those chars with more simple alternatives until you get a regex that matches part of your input, even if its not exactly the part you want - then expand it back out from there bit by bit until it breaks or you get what you want. If you find the point it breaks you should read up on the documentation for the language/tool you are using to figure out the syntax you are actually looking for.

  • Re your last sentence: or, just read the documentation for the tool/language you are using, and find out exactly which characters mean what. Instead of just guessing and muddling along. – Wildcard Aug 30 '17 at 5:54
  • Yes, sorry I alluded to using the documentation because it was obvious to me in this case but you are right you should consult the documentation once you have found the point at which the regex breaks. – Michael Daffin Aug 30 '17 at 9:13

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.