0

I have a file that shows the output of ls

e.g.

/home/john/A_2014.jpg /home/john/B_2014.jpg /home/john/C_2014.jpg /home/john/D_2014.jpg

now I want to use this output to write an insert script for mysql. I achieved to enter at the beginning and the end of every line the necessary code so the file actually looks like:

INSERT INTO myimages (name,picture) values ('#name',LOAD_FILE('/home/john/A_2014.jpg')); INSERT INTO myimages (name,picture) values ('#name',LOAD_FILE('/home/john/B_2014.jpg')); INSERT INTO myimages (name,picture) values ('#name',LOAD_FILE('/home/john/C_2014.jpg')); INSERT INTO myimages (name,picture) values ('#name',LOAD_FILE('/home/john/D_2014.jpg'));

Is there a way to substitute #name with the string that occurs before _2014.jpg so that the final output looks like

INSERT INTO myimages (name,picture) values ('A',LOAD_FILE('/home/john/A_2014.jpg')); INSERT INTO myimages (name,picture) values ('B',LOAD_FILE('/home/john/B_2014.jpg')); INSERT INTO myimages (name,picture) values ('C',LOAD_FILE('/home/john/C_2014.jpg')); INSERT INTO myimages (name,picture) values ('D',LOAD_FILE('/home/john/D_2014.jpg'));

Unfortunately there are to many lines to do this by hand. Thanks for your help.

Improve this question

3 Answers 3

Reset to default
1

Before

/home/john/A_2014.jpg /home/john/B_2014.jpg /home/john/C_2014.jpg /home/john/D_2014.jpg

Command

:%s@\v.*/([^/]+)_2014.*@...\1...&...@

After

...A.../home/john/A_2014.jpg... ...B.../home/john/B_2014.jpg... ...C.../home/john/C_2014.jpg... ...D.../home/john/D_2014.jpg...

As you can see:

  • use @ as delimiter
  • use \v to make regex very magic
  • use (...) to capture a group, then reference it as \1
  • use & to reference entire matched line
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Building on kev's answer, which shows how to use a back-reference, you also need to limit the text which will get replaced if you want to replace the "name" text.

Example for the entire file (using your :

:%s@\v.*'\zs#name\ze'.*/([^/]+)_2014.*@\1@

The \zs sets the starting position for the match, and the \ze sets the ending position. This lets you limit what text gets replaced. As kev mentioned, \1 in the replacement text refers to the text captured in the (...) group in the search pattern. The search pattern itself matches:

.* - any text ' - an opening literal single quote #name - the literal text, #name ' - the closing quote .* - any text again / - the final '/' in the line ( - start capturing a backreference [^/]+ - at least one non-slash character (matches A, B, etc.) ) - end backreference capture _2014 - matches this text literally .* - any other text

The replacement \1 replaces the entire match with the captured backreference, but remember we limited the match using \zs and \ze to only be the #name string.

Improve this answer

Comments

0

A "macro + substitution" solution…

  1. Put your cursor on the first line to edit, then record your macro in register q:

    qq 0/_2<CR> yh :s/#name/<C-r>"<CR> q

  2. Playback your macro:

    :+,$norm! @q<CR>
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.