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!