hierarchical queries

Thumbnail image for REGEX to Split a Comma-Separated String into Rows

REGEX to Split a Comma-Separated String into Rows

How to split a comma-separated string into rows in Oracle SQL, using regex function REGEX_SUBSTR and recursion via CONNECT BY.

Read the Full Article →
Hierarchical Data in a Relational Table

Flatten Hierarchical Data using a Single SQL

Single Oracle SQL to find and flatten hierarchical data stored in a table, using a combination of SYS_CONNECT_BY_PATH and regular expressions.

Read the Full Article →
Thumbnail image for LEVEL Pseudocolumn in Hierarchical Queries

LEVEL Pseudocolumn in Hierarchical Queries

LEVEL is a pseudocolumn (i.e. not a real column in the database but available in a query), which has a special function in hierarchical queries – it returns the position of any row in the hierarchy.

Read the Full Article →
Thumbnail image for Hierarchical Queries: A QuickStart Guide

Hierarchical Queries: A QuickStart Guide

A hierarchical query is one that works on data with a tree relationship. Oracle provides specialized keywords that work with hierarchical queries, such as START WITH…CONNECT BY, PRIOR and SYS_CONNECT_BY_PATH.

Read the Full Article →