Find and replace with Regex Capture Groups

Overview

A short introduction into the syntax of Regular Expression Groups that can be used for very common data transformation tasks. I personally often need them, when I use data from excel and need to extract something and put it into a list of strings, to use it for example in an SQL query like:

Name Column
A
B
C

select * from MyTable where name in ('A','B','C');

I usually execute them in IntelliJ / DataGrip with `STRG + F` and `STRG + R`. This should open, depending on your defined key mappings a mask like this:

Find-Replace-Mask in IntelliJ

Basics

Finding

Match text of the whole line from beginning to end

^\w*$

Capture the text of the whole line into a group by adding () to a segment

^(\w*)$

Capture the text of the whole line into a named group

^(?‹groupName›\w*)$

Adding multiple brackets into the regex will create multiple capture groups.

Replacing

Get capture by order

First group

$1

Second group

$2

and so on.

Get capture by name

${groupName}

Example

Convert column row entries from Excel to comma-separated strings, for example, to be used for range queries in SQL:

find:

^(\w*)$

replace:

'$1',