Skip to content
Coordinator DB
View as Markdown
View as Markdown

regexp ​

Description ​

Each input line is matched by regexp_pattern. Captures map positionally to target columns and convert to their types. regexp_skip_unmatched controls lines that do not match.

Input only. Select it with FORMAT regexp. Names ignore ASCII case; use the snake_case name shown here.

One complete physical line is matched at a time. Each capture group supplies the next target field; the capture's text is then decoded using regexp_escaping and converted to the target type.

The pattern must be supplied because its default is empty. A nonmatching line is an error unless regexp_skip_unmatched is enabled. This format has no output encoder.

text
INSERT INTO table [(columns)] [SETTINGS ...] FORMAT regexp
<data>

Example usage ​

sql
SELECT name,is_input,is_output FROM system.formats WHERE name='regexp' FORMAT csv;
text
"regexp",1,0

For lines such as id: 7, use SETTINGS regexp_pattern='id: ([0-9]+)' and one integer target column.

Format settings ​

Use a statement SETTINGS clause; these options are not persisted by SET.

Setting Type Default Meaning
regexp_escaping String Raw Read captured fields as Raw text, Escaped text, Quoted literals, CSV fields, or JSON values.
regexp_pattern String '' Match every complete input line; one capture group supplies each target column. Empty requires a statement override.
regexp_skip_unmatched Bool 0 Skip lines that do not match the expression; otherwise report their physical line number.
snappy_mode String basic Read Snappy client files as basic (Hadoop blocks) or framed (standard streaming format).

Load rows ​

Run the INSERT request separately; its remaining bytes are input data.

sql
CREATE TABLE regexp_input(id UInt8);
text
OK
sql
INSERT INTO regexp_input SETTINGS regexp_pattern='id: ([0-9]+)' FORMAT regexp
id: 7
text
OK
sql
SELECT * FROM regexp_input FORMAT csv;
text
7