IntermediateString Functions

JSON-like string parsing with SUBSTRING and POSITION

The query

SQL

-- Parse: "name=John;age=30;city=Mumbai"
WITH raw AS (
  SELECT 'name=John;age=30;city=Mumbai' AS data
)
SELECT
  SUBSTRING(data, POSITION('name=' IN data) + 5,
    POSITION(';' IN SUBSTRING(data, POSITION('name=' IN data) + 5)) - 1) AS name,
  SUBSTRING(data, POSITION('age=' IN data) + 4,
    POSITION(';' IN SUBSTRING(data, POSITION('age=' IN data) + 4)) - 1) AS age
FROM raw;

Tested against PostgreSQL 16

Note

“POSITION finds location, SUBSTRING extracts. Messy for complex data — migrate to JSONB when possible.