How to capture and replace strings with regex in Notepad++

Question

I try to capture and replace strings with regex in notepad++, but the result is not as expected. The database is formatted like this:

(602, '0602', '[{"id":"9","value":""},{"id":"1","value":"1"}]'),
(1644, '0164', '[{"id":"9","value":""},{"id":"1","value":"3"}]'),
(1311, '0131', '[{"id":"9","value":""},{"id":"1","value":"100"}]'),
(1321, '0132', '[{"id":"9","value":""},{"id":"1","value":"150"}]')

The task is to capture and place column 2 (values 0602, 0164, 0131, 0131) and to insert it as value of id 9. For example, end result must be like this:

(602, '0602', '[{"id":"9","value":"0602"},{"id":"1","value":"1"}]'),
(1644, '0164', '[{"id":"9","value":"0164"},{"id":"1","value":"3"}]'),
(1311, '0131', '[{"id":"9","value":"0131"},{"id":"1","value":"100"}]'),
(1321, '0132', '[{"id":"9","value":"0132"},{"id":"1","value":"150"}]')

I try with this regex in Notepad++ - Search:

(, '.*)("id":"9","value":"")

Replace:

($1)("id":"9","value":"$1")

The result is close, but not what I need. Please, help me if you know the right answer. Thank you in advance.

Answer 1

You can use below regex to capture the parts of the string and replace it by the second column value

^(\(\d+,\s*.*?(\d+).*?value":).*?([,}])

And use \1"\2"\3 as the replacement part.

Live Demo on RegEx101

Explanation:

1. ^: Start of line
2. \(: Match ( literal
3. \d+,\s*: Match digit/s followed by comma and any number of spaces
4. .*?: Match anything to satisfy the following condition
5. (\d+): Match one or more digits and add them in captured group
6. .*?value":: Match till value"
7. ([,}]): Match either , or }

The captured group \1 will contain the string till value":, "\2" will contain the number in the second column in double quotes and \3 will contain the last , or }.