Combining variable with submatch in Vim search and replace

Question

I have several lines similar to this this:

insert into team (lowernum, uppernum, position, color1, color2) values (15, 16, 32, "red", "green");

I want to perform a search and replace in vim to change the third number (108 in this example) on every line so that it is increased by 15. The resultant line therefore will look like this:

insert into team (lowernum, uppernum, position, color1, color2) values (15, 16, 47, "red", "green");

I have tried several search and replace options, but have not figured out how to combined variables with submatches to get my desired result. An example of what I have tried is here:

:%s/\((\d\d, \d\d, \)\(\d\d\)/\=\1 submatch(2) + 15/g

Obviously, this gives an error, but I haven't been able to figure out the correct way to implement the replace clause. What is the correct way to do this?

Answer

Based on romainl's answer, this is the solution that worked for me:

:%s/\d\{1,}, \d\{1,}, \zs\(\d\{1,}\)/\=submatch(0) + 15/g

zs indicates the start of the pattern that will be replaced.

Why not doing an update statement on the database instead? UPDATE team SET position = position + 15; — Patrick Günther
– Patrick Günther, Commented Jun 17, 2017 at 21:46
@PatrickGünther Because there is nothing in the database. These are scripts to populate a database. — kojow7
– kojow7, Commented Jun 17, 2017 at 22:26
But if there is nothing in the database, that's even better. Then you don't have to worry about affecting only the values, that are being inserted right now. Doesn't the import script also execute UPDATE statements? — Patrick Günther
– Patrick Günther, Commented Jun 17, 2017 at 22:33
@PatrickGünther, it is meant to be a clean database creation script. It would make no sense to populate the database with incorrect data and then fix it with update statements. Having a clean script is much nicer especially if I want to share it with others at a later time. romainl's answer solves my problem and does exactly what I was looking for. — kojow7
– kojow7, Commented Jun 17, 2017 at 22:41

romainl · Accepted Answer · 2017-06-17 22:26:58Z

3

This should do the trick:

:s/\d\{2,}, \d\{2,}, \zs\(\d\{2,}\)/\=submatch(0) + 15

answered Jun 17, 2017 at 22:26

romainl

199k21 gold badges300 silver badges340 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kojow7 Over a year ago

Thank you, you saved me huge amounts of time editing these lines one by one. :)

Patrick Günther · Accepted Answer · 2017-06-17 22:28:41Z

Having done a little bit of research, it doesn't seem possible to actually do arithmetics in search/replace operations in vim or sed. Though I would also be very interested if it actually is. I get, that what I suggested in my comment, an UPDATE statement would affect the whole table and not just the inserted values of that file.

So here is an alternative solution (assuming you're using mysql):

Replace every occurrence of 'team' in that file with 'tmp_team'.

Then add to the top of the file:

CREATE TABLE tmp_team LIKE team;

BEGIN EDIT: Now assuming that the team table has an auto_increment primary key on the column id, you have to drop that key in the tmp table and set all of it's values to null:

-- getting rid of the auto_increment property:
ALTER TABLE tmp_team MODIFY id INT DEFAULT NULL;
-- dropping primary key:
ALTER TABLE tmp_team DROP PRIMARY KEY;
UPDATE tmp_team SET id = NULL;

END EDIT

And to the bottom of the file:

UPDATE tmp_team SET position = position+15;
-- inserting NULL values into an auto_increment column, assigns
-- correct values to the new entries
INSERT INTO team SELECT * FROM tmp_team;
DROP TABLE tmp_team;

This way you would only affect the position values of the teams in that file.

Collectives™ on Stack Overflow

Combining variable with submatch in Vim search and replace

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related