I have searched unsuccessfully everywhere for an answer to this question and could not find an acceptable answer.

I am taking a list of table names from a database, and dynamically creating alter statements to change the database name.

What I am getting is something like the following:


I do a search and replace with:

 %s/^/alter table /

And end up with the following:

alter table call_center$
alter table catalog_page$
alter table catalog_returns$
alter table catalog_sales$
alter table customer$
alter table customer_address$
alter table customer_demographics$
alter table date_dim$
alter table household_demographics$
alter table income_band$
alter table inventory$
alter table item$

I run the following:

%s/\v(\S\s+)(\S+\s+)(\S+)$/\1\2\3\ rename to tpcds.\3;/

and end up with:

alter table call_center rename to tpcds.call_center;
alter table catalog_page rename to tpcds.catalog_page;
alter table catalog_returns rename to tpcds.catalog_returns;
alter table catalog_sales rename to tpcds.catalog_sales;
alter table customer rename to tpcds.customer;
alter table customer_address rename to tpcds.customer_address;
alter table customer_demographics rename to tpcds.customer_demographics;
alter table date_dim rename to tpcds.date_dim;
alter table household_demographics rename to tpcds.household_demographics;
alter table income_band rename to tpcds.income_band;
alter table inventory rename to tpcds.inventory;
alter table item rename to tpcds.item;

I can shorten this to:

%s/\v(\S\s+){1}(\S+)$/\1\2 rename to tpcds.\2;/

However, what I was never able to figure out, and need help with, how do you move a word, by its "position/index" to somewhere else in the line? I know how to do this if you are looking a specific word (like cat), but what if the word is always different, but in the same position?

For example, what if I were building select clauses, and wanted to change the following:

select cc_call_center_id, cc_name from call_center;
select i_item_id, i_name from item;


select cc_call_center_id, cc_name from call_center order by cc_call_center_id;
select i_item_id, i_name from item order by i_item_id;

How do you select the nth word (in this case, the 2nd) and append it somewhere else in the line (in this case, to the end of the line)?

*BTW, I realize I could just change it to "order by 1", but this is just an example.

Hope this makes sense. Thanks!


Personally, I would use :normal:

:%norm wyiwf;i order by <c-v><c-r>"

You can also use a substitution:

:%s/\w\+\s\+\(\w\+\).*\zs;/ order by \1;

I am using \zs to set the start of the match to simply the substitution. If you are having issue with substitutions then I suggest using traces.vim to help give visual feedback.

For more help see:

:h :norm
:h :range
:h c_CTRL-R
:h /\zs
  • Thanks for the quick response Peter. For the example I provided, this command worked: %s/\w\+\s\+\(\w\+\).*\zs;/ order by \1; However, it really doesn't help with how you would index the words. For example, what if I wanted to move the third word and insert it as the next to last word? Or, simply take the third word, and putting it at the end. Changing the number \1 to \2 does not work./ – Shanemeister Apr 23 '18 at 18:53
  • You will have to update the regex pattern. If you are going to do a bunch of crazy X number word, then I would suggest you look into awk – Peter Rincker Apr 23 '18 at 18:54
  • Thanks Peter. What I was hoping to find was some way of doing something similar to: %s/\v(\S\s+){1}(\S+)$/\1\2 rename to tpcds.\2;/ where I could have several patterns, and restructure the line with something like \3 \2 \1 \4/. For example, I want to repeat the first 3 words as a group (let's call it pattern 1), take the 4th word (call it pattern 2), and the remainder of the line (call it pattern 3), and then replace the line with /\1 \2 \3 \2. In other words, just copying the third word, and appending it to the same line -- as an example. Can this not be done using Vim? – Shanemeister Apr 23 '18 at 19:26
  • With a substitution you would have to craft your capture groups. :s/\(\w+\s\)\{3\}\(\w\+\)\s.*\zs/ \2/ for you copy 4th word put at the end. or :norm 3wyiwA <c-v><c-r>". If you are doing this with awk: %!awk '{ print $0, $4}'. If you wanted to do this in vimscript you would do :g/^/let a = split(getline('.'), '\s\+')|call setline('.', join(a + [a[3]], ' ')). You can of course you any programming language of choice to do these manipulations as well, perl/ruby/awk/python/etc. You can use :! in Vim to filter through a program. – Peter Rincker Apr 23 '18 at 19:42
  • Yes, always more than one way to solve a problem. Need vim for down and dirty quick modifications to files, and hoping to learn more. Perl/Python/R are always good options for more detailed, or repeatable processes. Can you provide a line that would be modified by: :s/\(\w+\s\)\{3\}\(\w\+\)\s.*\zs/ \2/ ? I am not tracking with that command. Thanks for your quick response! – Shanemeister Apr 23 '18 at 20:04

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.