Find the nth word in a line, and replace it back into the line in different position

Question

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:

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

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;

to:

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!

Answer 1

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