JParq

JParq is a JDBC driver for Apache Parquet files. It treats a directory as a database and every .parquet file in that directory as a table. The table name is the filename without the .parquet extension. Sub directories are treated as schemas. For example, given the following directory structure:

/data
  ├── employees.parquet
  ├── departments.parquet
  └── sales
      ├── orders.parquet
      └── customers.parquet   

The tables employees and departments would be in the default PUBLIC schema, while orders and customers would be in the sales schema.

JParq uses Apache Arrow and Apache Parquet for efficient columnar reads and jsqlparser to parse SQL statements. It aims to be 100% compliant with the read part of the SQL standard. There are a few common extensions supported as well e.g. LIMIT, ASCII, REPEAT, DIFFERENCE.

Note: When common implementations differ from the SQL standard, we stick to the standard. An example of this is convert which in many databases is used for data type casting, but in the SQL standard is used for character set conversion. If you want to convert data types, use CAST instead.

Note The majority of the code was created in collaboration with (vibe coded with) ChatGPT Codex with Copilot and myself (Per Nyfelt) as code reviewers.

Requirements

• Java 21. This matches the client runtime supported by Hadoop 3.5.0. Java 25 passes JParq's test suite on macOS ARM64 and can be used on a best-effort basis, but Hadoop 3.5.0 does not officially support it.
• Parquet files stored in a directory accessible from the JVM running the driver

Installation

Add the dependency to your build. Replace x.y.z with the latest version number from the Maven Central badge above.

<dependency>
  <groupId>se.alipsa</groupId>
  <artifactId>jparq</artifactId>
  <version>x.y.z</version>
</dependency>

Usage

The driver registers itself when the se.alipsa.jparq.JParqDriver class is loaded, so simply placing the JAR on the classpath is normally enough. If your runtime requires explicit registration you can call Class.forName("se.alipsa.jparq.JParqDriver") before obtaining a connection.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import se.alipsa.jparq.JParqSql;

public class JParqExample {

  // Standard JDBC
  void selectMtcarsLimit() throws SQLException {
    String jdbcUrl = "jdbc:jparq:/home/user/data";
    try (Connection conn = DriverManager.getConnection(jdbcUrl);
         Statement stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery("SELECT * FROM mtcars LIMIT 5")) {
      while (rs.next()) {
        System.out.println(rs.getString(1));
      }
    }
  }

  // Using the JParqSql helper
  void selectMtcarsToyotas() {
    String jdbcUrl = "jdbc:jparq:/home/user/data";
    JParqSql jparqSql = new JParqSql(jdbcUrl);
    jparqSql.query("SELECT model, cyl, mpg FROM mtcars WHERE model LIKE 'Toyota%'", rs -> {
      try {
        while (rs.next()) {
          System.out.println(rs.getString(1) + ", " + rs.getInt(2) + ", " + rs.getDouble(3));
        }
      } catch (SQLException e) {
        System.out.println("Query failed: " + e);
      }
    });
  }
}

Subqueries resolve unqualified column names in their inner scopes first, then in the outer row or grouping context when the inner inputs have no matching column. This also applies to correlations in HAVING. Virtual information_schema tables use their fixed schemas for name binding. If an inner input's columns cannot be determined, unqualified names remain inner; use an explicit qualifier for outer references. WITH declarations inside subqueries, including derived tables, keep their local scope and override physical tables or inherited CTEs with the same name.

Connection options

• caseSensitive — defaults to false. Set to true to make table-name resolution case-sensitive.

  jdbc:jparq:/home/user/data?caseSensitive=true
  

• datasetLayout — defaults to none (one physical file per table). Set to hf to group Hugging Face shards and numeric directory layouts into logical tables. Values are case-insensitive (HF and None work); empty, whitespace-padded, and unknown values fail connection creation with the allowed values.

  jdbc:jparq:/datasets/example?datasetLayout=hf
  

• Paths may be specified directly or using the file:// prefix.
• predicatePushdown — defaults to true. Set to false to evaluate the entire WHERE clause in Java.
• projectionPushdown — defaults to true. Set to false to read every column, while still validating column names.

  jdbc:jparq:/home/user/data?predicatePushdown=false&projectionPushdown=false
  

Hugging Face optimized Parquet

JParq reads ordinary and Hugging Face optimized Parquet files, including files written by PyArrow with content-defined chunking (CDC). CDC changes page boundaries; it does not require a special reader. When a file contains a page index, JParq uses it automatically with supported predicates to skip pages within row groups. The CLI /describe <table> reports row groups, row counts, and page-index availability. /info reports the connection's pushdown settings.

Pushdown supports column-to-literal comparisons, BETWEEN, IN (up to 20 literals), IS NULL, IS NOT NULL, and supported AND/OR/NOT combinations. Supported terms of an AND are pushed independently; an OR must be fully supported. A case-sensitive string LIKE 'prefix%' can use a UTF-8 prefix range, with the exact pattern still evaluated in Java. ILIKE, escaped patterns, and prefixes containing _ remain in Java. Date, timestamp, and exactly representable decimal literals are supported; incompatible or out-of-range bounds remain in Java. DISTINCT queries can use filters too.

Floating-point values use one order everywhere, with and without pushdown, matching PostgreSQL and DuckDB: -Infinity < every finite value < +Infinity < NaN, where NaN = NaN and -0.0 = 0.0. Comparisons with NaN and infinities are therefore TRUE or FALSE rather than UNKNOWN, and ORDER BY, MIN and MAX place NaN last. PyArrow leaves NaN out of Parquet statistics, so on FLOAT and DOUBLE columns only <, <=, =, IN and BETWEEN against a non-zero literal are pushed down; >, >=, <>, NOT IN and NOT BETWEEN are evaluated in Java. With Parquet 1.18, floating-point page indexes without NaN counts retain all pages for range predicates; equality and IN can still prune pages, as can eligible predicates on other columns. Arithmetic on NaN or infinities and DISTINCT/GROUP BY of -0.0 versus 0.0 are not yet covered.

Single-table conditions in a join's WHERE can filter individual inputs. Unqualified columns must resolve to one input. Outer-join filtering preserves NULL-extended rows, and merged FULL JOIN ... USING columns remain in the final filter. The complete WHERE is always evaluated after the join. Predicate pushdown is disabled for sampled inputs: TABLESAMPLE chooses from the unfiltered input before WHERE, so REPEATABLE samples are independent of the pushdown setting. ORDER BY projections retain sorting and correlated-subquery inputs while excluding unrelated columns such as embedded media.

Nanosecond timestamps, local timestamps, float16, lists, and image structs are readable. UTC-adjusted timestamps are returned as java.sql.Timestamp instants; local timestamps retain their stored wall-clock values in the JVM's default time zone. Microsecond and nanosecond precision is preserved. Lists are available through ResultSet.getObject, and structs through their JSON string representation. Shards remain separate tables by default; opt into logical multi-file tables with datasetLayout=hf as described below.

Small PyArrow fixtures are committed, so Maven does not need Python. Regeneration instructions and the exact PyArrow dependency are in tools/createHfOptimizedData.py.

Hugging Face dataset layouts

The detailed requirements and verification record are in hf-dataset-layout.md.

With datasetLayout=hf, point the URL at the dataset root to expose these layouts:

Relative files	Logical table
data/train-0-of-2.parquet, data/train-1-of-2.parquet	data.train
default/train/0000.parquet, default/train/0001.parquet	default.train
train-0-of-1-a1b2.parquet	PUBLIC.train

Direct base files belong to PUBLIC. An immediate child containing only numeric Parquet files and no visible child directories becomes a PUBLIC table, rather than a schema. Otherwise it remains a schema, whose direct files and eligible immediate numeric directories become tables. README files do not disqualify a directory; a visible partial-train/ child or nonnumeric Parquet file does. Empty directories are schemas, not tables. All dot-prefixed entries are ignored in HF mode, including .cache/ and .ipynb_checkpoints/. The explicitly chosen base is honored even if its own name starts with a dot. The default none layout retains its existing behavior.

Pointing at default/ in the example exposes PUBLIC.train without a train schema. Pointing at data/ exposes named splits as PUBLIC tables. Dataset-root data/config/train-*.parquet is too deep and is not discovered; point at data/ to expose config.train. There are no nested schemas or deeper traversal. Pointing at default/train/ itself exposes its individual PUBLIC.0000/PUBLIC.0001 files.

Named shards match this exact regex (optional hexadecimal hash suffix):

^(?<split>[A-Za-z0-9_][A-Za-z0-9_.-]*)-(?<index>[0-9]+)-of-(?<total>[0-9]+)(?:-(?<hash>[0-9a-fA-F]+))?\.(?i:parquet)$

A group requires a positive common total and exactly one index from zero through total minus one. Missing, duplicate (including padded/hash variants), out-of-range, and disagreeing-total shards fail discovery. Nonmatching filenames remain ordinary single-file tables. Directory-table names match ^[0-9]+\.(?i:parquet)$; gaps are allowed but duplicate numeric indices are errors. Both layouts scan in numeric order (9 before 10). SQL row order still requires ORDER BY. Bounds, DISTINCT, aggregation, windows, joins, and repeatable sampling operate over the whole logical input. Each shard retains predicate and projection pushdown, its own physical read schema and enriched logical types, and at most one shard reader is open per logical input. Statement.cancel() applies while executeQuery() is aggregating, sorting, loading joins, or executing nested queries, as well as after a result has been returned. It requests closure without waiting for an active physical read. The executing executeQuery() or in-flight next() throws SQLException when cancellation is observed, instead of returning a partial aggregate or silently reporting end of results. Cancellation exceptions use SQLState 57014, including cancellation inside a correlated subquery evaluated during iteration. Nested queries share the outer cancellation state and preserve the outer result returned by Statement.getResultSet(), so cancellation between subquery calls also stops iteration. The reading thread closes its input before opening another shard. A pushed-down record filter can make the current physical read span the rest of a shard; cancellation does not interrupt a decoder in use.

File and directory symlinks are followed, including visible .parquet links to extensionless cache blobs outside the base. Names and discovery depth come from the visible paths. Dot-prefixed links are ignored; other dangling non-Parquet links such as README are ignored unless they resolve to directories. Visible invalid .parquet links fail discovery. Unlistable discovered directories fail too. HF mode rejects table-source collisions (single file, group, or directory) and normalized schema collisions, even for empty/README-only schema directories. Case variants collide with caseSensitive=false; they remain distinct with true on supporting filesystems. A base directory table is assigned only the table role, so its own name does not also create a schema.

Discovery reads no footers and is catalog-wide: one incomplete group, naming collision, or broken Parquet link fails even an unrelated table query or filtered catalog listing. Repairing it lets the next discovery succeed. Schema validation is lazy and compares field names/order, types, nullability, containers, enum symbols, fixed sizes, and logical-type parameters recursively. Union branch order, record names/namespaces, docs, defaults, and nonsemantic writer metadata may differ. There is no schema widening or evolution. Each query input is fully validated before returning rows. A prepared statement reuses its validated schemas across executions; validation is not shared between prepared statements. An incompatible group does not prevent a healthy physical-table query.

getTables, CLI /list, and information_schema.tables read remarks from the first file only, without full shard validation. First-file footer I/O errors can still fail a listing. getColumns validates every shard of every matching table; /describe and queries validate their selected inputs. Every information_schema.columns query materializes all catalog columns before SQL WHERE: even a filter naming a healthy table fails if another table is incompatible. Wildcard getColumns(null, null, "%", "%") has the same scope. Column requests cost a footer read per included shard, repeated for new statements; no shared validation cache is used. A direct getColumns call restricted to a healthy table remains independent. Table/column remarks come from the first numeric file. /describe sums files, row groups, and unfiltered row counts and reports page indexes as present (all chunks have both indexes), absent (none have either, including no chunks), or partial (anything else).

Dots and hyphens can occur in table names. Use schema-qualified SQL for dotted names: data."train.v2" or PUBLIC."train.v2". The existing parser does not resolve unqualified "train.v2", even with an alias. Unqualified "train-extra" and "train-extra" t work. With the default caseSensitive=false, lowercase both schema and table names before quoting names obtained from listings: listed Train-X becomes data."train-x". Leave PUBLIC unquoted or use "public"; quoted "PUBLIC" does not resolve in this mode. With caseSensitive=true, use exact listed spelling, such as data."Train-X" or "PUBLIC"."train.v2". Blindly quoting original mixed-case listed names is therefore unsuitable with the default setting.

CLI /describe takes literal names and escapes JDBC metadata wildcards (_, %, and the escape character) before requesting columns. It describes one exact table, using PUBLIC when no schema is given. Its plain command syntax splits at the first dot: /describe data.train.v2 describes table train.v2 in schema data. For a dotted PUBLIC table use /describe PUBLIC.train.v2; /describe train.v2 means schema train, table v2. These command arguments use no SQL quotes. /describe train-extra needs no schema for a PUBLIC table.

TableLocation now has an immutable nonempty List<File> files record component. Its original single-file constructor and file() accessor remain available; file() returns only the first file. Code inspecting record components must account for this change, and logical-table consumers should use files().

Fat Jar

A "fat" jar, which includes all dependencies, is also created. This is useful when you want to add the driver to the classpath of a tool. The fat jar is named jparq-x.y.z-fat.jar i.e. fat is the maven classifier. It is available in maven central and in the releases section.

SQL clients

Use jparq-x.y.z-fat.jar as a custom/generic JDBC driver. The ordinary JAR requires all runtime dependencies; adding that small JAR alone is insufficient for connections and queries.

Setting	Value
Driver class	se.alipsa.jparq.JParqDriver
JDBC URL	jdbc:jparq:/absolute/path/to/parquet-directory
Windows URL example	jdbc:jparq:C:/data/parquet
Credentials	Leave username and password empty
Client JVM	Java 21 supported; Java 25 best effort; Java 17 cannot load the driver

Check the Java runtime running the client, including any bundled JVM. JParq remains compiled for Java 21; a scanner running on Java 17 can reject its classes before offering a driver selection. Java 25 results do not extend Hadoop's official Java support. Use read-only, forward-only result sets when the client offers that choice.

These setup recipes use the clients' custom-driver facilities; their GUI workflows have not been verified. SQL Workbench/J build 133 also has a tested headless batch integration (see the verification record).

• DBeaver: in Database → Driver Manager, create a generic driver named JParq. Add the fat JAR under Libraries, set the driver class above, and use the complete JDBC URL when creating a connection. See DBeaver's driver manager documentation.
• DbVisualizer: create a user driver from the Custom template in Driver Manager, add the fat JAR, set Driver Class, and configure a connection with the JDBC URL above. See DbVisualizer's installation guide.
• SQuirreL SQL: create a driver definition with the fat JAR on its extra classpath and the class above; create an alias using that driver and the complete JDBC URL.
• SQL Workbench/J: add a driver definition with the fat JAR and class above, then select it in a connection profile and enter the complete JDBC URL.

Browse schemas, tables, and columns, then try SELECT * FROM salary LIMIT 5 when the directory contains salary.parquet. Close and reconnect to check the client's full workflow. If discovery or browsing fails, record the client version, its actual Java version, the JAR version, and the full exception. A successful isolated JDBC probe does not establish compatibility with a specific GUI client's generated SQL or scanner.

The compatibility verification record lists tested runtimes, platforms, loader scenarios, and remaining limits, including x64 coverage.

SQL Workbench/J can run scripts without a GUI. To include its real client loader and batch execution in the packaged-driver tests, install it separately from the official download, build JParq, then run:

mvn -Dit.test=JParqPackagedDriverIT -Djparq.it.sqlWorkbenchJar=/path/to/sqlworkbench.jar failsafe:integration-test failsafe:verify

The optional test lists schemas/tables, describes columns, queries all fixture codecs, opens a second batch session, and checks that a deliberate query failure aborts the script. Without this property it is reported as skipped. It uses the selected jparq.it.javaHome runtime and needs Java 21 or later for build 133. The client is not bundled, downloaded during Maven builds, or added to JParq's application classpath.

JParq CLI

An interactive command line interface is available for querying Parquet files using JParq. The CLI supports command history and basic line editing features. It also includes several commands starting with a slash (/) for controlling the CLI itself. To start the CLI, run the following command, replacing path/to/jparq.jar with the actual path to the JParq fat jar and the {path to jparq dir} with the path the directory where your parquet files reside.

java -jar path/to/jparq.jar {path to jparq dir}

To download the fat jar, you can either download it from releases or use the following command:

LATEST_VERSION=$(curl -s https://repo1.maven.org/maven2/se/alipsa/jparq/maven-metadata.xml | sed -nE 's/.*<release>(.*)<\/release>.*/\1/p') && \
curl -o jparq.jar https://repo1.maven.org/maven2/se/alipsa/jparq/$LATEST_VERSION/jparq-$LATEST_VERSION-fat.jar

For simpler access you can create a script to it (e.g called jparq) that you put in your ~/bin dir

#!/usr/bin/env bash
java -jar /path/to/jparq.jar $@

Once the CLI is started, you can use the following commands:

• /connect - Connect to a Parquet database directory (dir with parquet files)
• /close - Close the current connection
• /list - List available tables
• /describe - Show column definitions for a table
• /info - Show information about the current connection
• /help - Display this help text
• /exit - Exit the CLI

Anything else will be treated as a SQL query and executed against the connected dir.

Example session:

$ java -jar ~/lib/jparq.jar target/test-classes/acme
JParq CLI version 1.4.0
Connected to /home/user1/project/JParq/target/test-classes/acme
jparq(acme)> select * from employees e left join salary s on e.id = salary.employee
e__id | first_name | last_name  | s__id | employee | salary   | change_date
------+------------+------------+-------+----------+----------+------------
1     | Per        | Andersson  | 1     | 1        | 150000.0 | 2020-03-01 
1     | Per        | Andersson  | 6     | 1        | 160000.0 | 2021-01-01 
1     | Per        | Andersson  | 8     | 1        | 165000.0 | 2021-08-01 
2     | Karin      | Pettersson | 2     | 2        | 180000.0 | 2020-03-01 
3     | Tage       | Lundström  | 3     | 3        | 130000.0 | 2021-01-01 
3     | Tage       | Lundström  | 7     | 3        | 140000.0 | 2021-12-01 
4     | Arne       | Larsson    | 4     | 4        | 195000.0 | 2020-10-01 
5     | Sixten     | Svensson   | 5     | 5        | 230000.0 | 2020-12-15 
jparq(acme)> /exit
$

JDBC support

• Driver scope — Read-only. Only SELECT statements are supported; executeUpdate/DML, generated keys, and updatable result sets are not implemented. Result sets are TYPE_FORWARD_ONLY, CONCUR_READ_ONLY with CLOSE_CURSORS_AT_COMMIT holdability.
• Prepared statements — SQL is planned when the PreparedStatement is created. Positional ? parameters are bound via the standard setXxx methods and rendered as safe SQL literals (named parameters are also supported). Parameter metadata is available but reports generic VARCHAR/IN characteristics because actual typing occurs at execution. Only forward-only/read-only statements with CLOSE_CURSORS_AT_COMMIT holdability are accepted; overloads requesting other result-set types or generated keys/column-based key selection throw SQLFeatureNotSupportedException. Prepared-statement batching without a SQL argument is supported for convenience and reports SUCCESS_NO_INFO counts per entry. Statement batching and addBatch(String) remain unsupported.
• Parquet predicate pushdown — JParq attaches supported WHERE predicates to Parquet readers and enables statistics, column-index, dictionary, and bloom-filter pruning. Supported AND terms can be pushed independently, with unsupported terms evaluated in Java. See the Hugging Face optimized Parquet section above for supported operators and join/sample behavior. JParqPreparedStatement.getPushdownInfo() reports the attached predicate and the remaining residual filter.
• Transactions and sessions — The connection is always read-only with TRANSACTION_NONE; commit, rollback, and savepoints are no-ops. getAutoCommit() always returns true, and changing isolation level, holdability, or poolability is not supported.
• Metadata — DatabaseMetaData exposes tables and columns derived from the base directory and subdirectories (schemas); the catalog is the base directory name. Callable statements, stored procedures, and custom type mappings are not supported.
• Other JDBC surfaces — CallableStatement, createClob/createBlob/createArrayOf, network timeout, and client-info features are not implemented. Connection validation via isValid() returns true while the connection is open and false after it is closed.

Non standard JDBC extensions

• Named parameters i.e. :paramName syntax for prepared statements

SQL Support

JParq aims to be fully compliant with the read part of the SQL standard (SQL:2016 and earlier). As of version 1.1.1, the support for the standard is complete (as far as I know). The following is a detailed list of supported features and functions.

The following SQL statements are supported:

• SELECT with support for
  • * to select all columns
  • Qualified wildcard projections (table.*).
  • alias support for columns and tables
  • Support computed expressions with aliases (e.g. SELECT mpg*2 AS double_mpg)
  • Quoted identifiers using "double quotes" as per the SQL standard.
  • CASE support
• SELECT statements with WHERE supporting:
  • BETWEEN, IN, LIKE operators
  • AND, OR, NOT logical operators
  • Comparison operators: =, !=, <, >, <=, >=
  • Null checks: IS NULL, IS NOT NULL
• ORDER BY clause with multiple columns and ASC/DESC options
  • support for optional explicit NULLS FIRST and NULLS LAST
• OFFSET and LIMIT support
  • Also support the PostgreSQL shorthand LIMIT n OFFSET m syntax
• Standard row-limiting syntax (FETCH FIRST / OFFSET … FETCH)
• DISTINCT support
• Functions support
  • Date functions
  • Aggregate functions (count, sum, avg, max, min)
  • CAST support
  • CONVERT support (for both character set conversion with USING and data type conversion without USING, as per SQL standard and common extensions)
  • coalesce (The COALESCE() function returns the first non-null value in a list.)
  • String functions (all SQL standard string function supported)
    • also support || operator for string concatenations
  • Numeric functions (abs, ceil/ceiling, floor, round, sqrt, truncate/trunc, mod, power/pow, exp, log, log10, rand/random, sign, sin, cos, tan, cot, asin, acos, atan, atan2, degrees, radians, pi)
  • ARRAY constructor function
• comments (line --) and block (/* */)
• Subquery support
  • In the SELECT Clause : Used to return a single value or a set of values. e.g. SELECT first_name, ( SELECT department_name FROM departments WHERE departments.department_id = employees.department_id ) AS department_name FROM employees;
  • In the FROM Clause : Treated as a derived table or inline view. E.g: SELECT * FROM (SELECT first_name, salary FROM employees WHERE salary > 5000) AS "high_salaried"
  • In the WHERE Clause : Used to filter the results. e.g SELECT first_name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id>1500);
  • In the HAVING Clause : Used to filter groups. E.g: SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);
• GROUP BY support
  • COUNT(*) aggregation
  • HAVING clause with conditions
  • support aggregation functions and case statements in the GROUP BY and SELECT clause
• EXISTS support
• ANY and ALL support
• Join support: INNER, LEFT, RIGHT, FULL, CROSS, and Self Join, join ... using syntax
• UNION and UNION ALL support
• Complete set-operation coverage.
  • EXCEPT
  • INTERSECT
  • INTERSECT ALL
  • EXCEPT ALL
  • nesting of set operations
• CTE (Common Table Expressions) support
• Windowing
  • Ranking functions
    • ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE
  • Aggregate window functions
  • SUM, AVG, MIN, MAX, COUNT
  • Analytic Value/Navigation Functions
    • LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE
• Advanced GROUP BY constructs i.e:
  • GROUPING SETS
  • ROLLUP
  • CUBE
• Derived Tables: UNNEST with or without a table wrapper, LATERAL derived tables, VALUES table constructors
• INFORMATION_SCHEMA.COLUMNS and INFORMATION_SCHEMA.TABLES

String functions support details

Character Length and Position

• CHAR_LENGTH(string) or CHARACTER_LENGTH(string) Returns number of characters in a string. CHAR_LENGTH('hello') → 5
• OCTET_LENGTH(string) Returns number of bytes in the string (depends on encoding). OCTET_LENGTH('Å') → 2 (in UTF-8)
• POSITION(substring IN string) Finds the position (1-based) of substring in string. POSITION('l' IN 'hello') → 3

Substrings and Extraction

• SUBSTRING(string FROM start [FOR length]) Extracts substring starting at start, optionally limited by length. SUBSTRING('abcdef' FROM 2 FOR 3) → 'bcd'
• LEFT(string, count) (optional extension) Leftmost characters. LEFT('abcdef', 3) → 'abc'
• RIGHT(string, count) (optional extension) Rightmost characters. RIGHT('abcdef', 2) → 'ef'

Concatenation

• CONCAT(string1, string2, …) Concatenates two or more strings (SQL:2016 added variadic support). CONCAT('a','b','c') → 'abc'
• Support for the || operator, covering both binary and text concatenation, as well as null propagation.

Case Conversion

• UPPER(string) Converts to uppercase. UPPER('sql') → 'SQL'
• LOWER(string) Converts to lowercase. LOWER('SQL') → 'sql'

Trimming and Padding

• TRIM([LEADING TRAILING BOTH] [characters] FROM string)
• LTRIM(string) (extension) Trims leading spaces. LTRIM(' hi') → 'hi'
• RTRIM(string) (extension) Trims trailing spaces. RTRIM('hi ') → 'hi'
• LPAD(string, length [, fill]) (SQL:2008 optional) Pads string on the left. LPAD('42', 5, '0') → '00042'
• RPAD(string, length [, fill]) (SQL:2008 optional) Pads string on the right. RPAD('42', 5, '0') → '42000'

Searching and Replacing

• OVERLAY(string PLACING replacement FROM start [FOR length]) Replaces part of string starting at start with replacement. OVERLAY('abcdef' PLACING 'xyz' FROM 3 FOR 2) → 'abxyze f'
• REPLACE(string, search, replace) (SQL:2008) Replaces all occurrences of search with replace. REPLACE('banana', 'na', 'xy') → 'baxyxy'

Collation and Comparison

• COLLATE(string, collation_name) Applies a specific collation to a string. 'abc' COLLATE "sv_SE"
• SIMILAR TO e.g. 'cat' SIMILAR TO '(cat|dog)' → TRUE 'cab' SIMILAR TO 'c(a|o)b' → TRUE 'cab' SIMILAR TO 'c(a|e)b' → FALSE 'abc' SIMILAR TO 'a%' → TRUE
• REGEXP_LIKE (pattern matching operators) e.g: REGEXP_LIKE('abc123', '^[a-z]+[0-9]+$') → TRUE REGEXP_LIKE('AbC', 'abc', 'i') → TRUE -- 'i' = case-insensitive REGEXP_LIKE('cat', 'dog|cat') → TRUE

Unicode and Codepoints

• CHAR(code) Returns the character corresponding to a code point. CHAR(65) → 'A'
• UNICODE(string) Returns Unicode code point of first character. UNICODE('A') → 65

SQL:2016–2023 Additions

• NORMALIZE(string [USING form]) Normalizes Unicode text (SQL:2016). NORMALIZE('é') → 'é'
• STRING_AGG(expression, separator) Aggregates values into a single string with a separator. STRING_AGG(name, ', ') → 'Alice, Bob, Carol'
• JSON_VALUE, JSON_QUERY, JSON_OBJECT, JSON_ARRAY JSON construction/extraction—technically not core string functions but string-returning functions standardized in SQL:2016–2023.

Non-standard extensions

• ASCII(string) Returns the ASCII code of the first character. ASCII('A') → 65
• LOCATE(substring, string[, start]) Locates substring in string with optional start position. LOCATE('c', 'abcabc', 3) → 3
• REPEAT(string, count) Repeats the string N times. REPEAT('a', 4) → 'aaaa'
• SPACE(count) Creates a string of N space characters. SPACE(5) → ' '
• INSERT(string, start, length, replacement) Inserts replacement at position after removing length characters. INSERT('abcdef', 3, 2, 'XYZ') → 'abXYZef'
• SOUNDEX(string) Computes the Soundex phonetic code. SOUNDEX('Robert') → 'R163'
• DIFFERENCE(string1, string2) Calculates similarity based on Soundex codes (0-4). DIFFERENCE('Smith', 'Smyth') → 4

Build and test

mvn verify

• To run without spotless, add -Dspotless.check.skip=true
• To skip unit tests, add -DskipTests
• To skip integration tests add -DskipITs=true

This project uses Checkstyle, PMD, and Spotless. The checks run automatically as part of the Maven build. Use the spotless:apply goal before committing if you need to fix formatting issues.

Release notes

See release.md for the full version history and work in progress.

Roadmap: Might be implemented in the future

Non standard extensions

• Allow omission of from clause in some cases
  • Several popular databases allow you to omit the FROM clause when you are only selecting literal values, performing arithmetic, or evaluating scalar functions (functions that return a single value). In these cases, the query returns a single row e.g: SELECT CURRENT_DATE as cur_date.
• Support for variable assignment and use within SQL scripts.
  • @variable_name syntax to define a variable that exists for the duration of the connection
    • Example (direct assignment, connection scope): declare @myVar INT = 10; SELECT * FROM myTable WHERE myColumn > @myVar; SELECT * FROM anotherTable LIMIT @myVar;
• PIVOT and UNPIVOT operators.
• TABLESAMPLE clause for sampling rows from a table.
• Support for modular encryption via a keystore and/or a Key Management Service

Out of scope (will not be supported, at least not in the foreseeable future)

• Data modification statements (INSERT, UPDATE, DELETE, MERGE)
• Transaction control (COMMIT, ROLLBACK, SAVEPOINT)
• Data definition statements (CREATE, ALTER, DROP, TRUNCATE)
• User management and security (GRANT, REVOKE, CREATE USER, etc.)
• Stored procedures and functions (CREATE PROCEDURE, CREATE FUNCTION)
• Triggers (CREATE TRIGGER, DROP TRIGGER)
• Advanced indexing and optimization hints
• Full-text search capabilities
• TEMPORARY TABLES, you need to use CTE's or value tables instead.