MeridianMERIDIAN

Pipe-Separated Values

container.array.pipe_separated

List separated by pipe character: value1|value2|value3. Similar to CSV but uses pipe as delimiter.

Worked samples

DuckDB 1.5.5 · finetype 0.6.64

ValueAs VARCHARparts
apple|banana|cherryapple|banana|cherry['apple', 'banana', 'cherry']
1|2|3|4|51|2|3|4|5['1', '2', '3', '4', '5']

Shape

Pattern
^[^|]+(\|[^|]+)*$
ValueLengthShape
apple|banana|cherry19matches
1|2|3|4|59matches

Told apart

3 of the 3 other array types accept every one of these samples on shape alone.

Going the other way, this type's own rule accepts 3 of 3 samples of Comma-Separated Values (Simple), 2 of 2 samples of Semicolon-Separated Values and 3 of 3 samples of Whitespace-Separated Values.

Background

Origin

FineType defines pipe-separated values as its own type, labelled container.array.pipe_separated, for a list held in a single value with the elements separated by the pipe character, as in value1|value2|value3.[1] FineType's definition describes the type as similar to CSV but using the pipe as the delimiter.[1] FineType's validation pattern for the type, ^[^|]+(\|[^|]+)*$, requires every element between pipes to be non-empty.[1]

The pipe belongs to a wider family of delimiter-separated values, where any character may separate values.[2] Wikipedia lists the vertical bar, also known as the pipe, among the more commonly used separators, alongside the comma, tab, colon and space.[2]

Use

Delimiter collision happens when the delimiter character appears inside a value without any accommodation, so that one logical value is split into two.[2] Some delimiter-separated conventions simply disallow the delimiter inside a value, while others provide a way to embed it.[2] Before splitting, check whether any element could itself contain a pipe, and find out from the producer how such a pipe would be written. (Meridian's view)

Python's re documentation says that to match a literal pipe you write \| or enclose it in a character class, as in [|].[3] Split on the pipe as a literal string where you can, and escape it whenever a regular expression is involved. (Meridian's view)

Where you'll meet it

The GroupLens MovieLens ml-latest-small dataset stores each film's genres in movies.csv as a pipe-separated list.[4] The MovieLens ml-latest-small README lists (no genres listed) among the values the genres are selected from.[4] Treat a placeholder like (no genres listed) as an empty list rather than a genre before counting or exploding the genres. (Meridian's view)

Sources

  1. [1]finetype/labels/definitions_container.yaml at main · meridian-online/finetypeFineType
  2. [2]Delimiter-separated values - Wikipediasecondary source
  3. [3]re — Regular expression operationsdocumentation
  4. [4]ml-latest-small-README.htmldataset

Detection notes

v1 migration: New for v2. Pipe-separated format common in legacy databases and log files. Decompose splits on pipe.

Use it

CLI · DuckDB
$ finetype infer -i "apple|banana|cherry" --mode column
→ container.array.pipe_separated

SELECT ft_infer('apple|banana|cherry');
-- → 'container.array.pipe_separated'
Cast expression
CAST({col} AS VARCHAR)

Schema

https://meridian.online/schemas/container.array.pipe_separated · Frictionless type string

finetype taxonomy container.array.pipe_separated -o json-schema

Aliases

pipe_list

Type Registry