AdvancedString Functions

Parse structured strings into columns

The query

SQL

-- Assume format: "DEPT:Engineering|LEVEL:Senior|YEARS:5"
SELECT
  metadata,
  SPLIT_PART(SPLIT_PART(metadata, 'DEPT:', 2), '|', 1) AS department,
  SPLIT_PART(SPLIT_PART(metadata, 'LEVEL:', 2), '|', 1) AS level,
  SPLIT_PART(SPLIT_PART(metadata, 'YEARS:', 2), '|', 1)::INT AS years
FROM employee_metadata;

Tested against PostgreSQL 16

Note

“String parsing for legacy systems storing structured data in single columns. Better: use JSONB.