New versions: Dev (3.14) | Latest (3.13) | 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | Old versions: 3.7 | 3.6 | 3.5 | 3.4 | 3.3

SUBSTRING

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The SUBSTRING() function calculates the substring of a string given a starting position and optionally, a length.. See also LEFT, RIGHT

SELECT 
  substring('hello world', 7),
  substring('hello world', 7, 1);
create.select(
  substring("hello world", 7),
  substring("hello world", 7, 1)).fetch();

The result being

+-----------+-----------+
| substring | substring |
+-----------+-----------+
| world     | w         |
+-----------+-----------+

Dialect support

This example using jOOQ:

substring(val("hello world"), 7)

Translates to the following dialect specific expressions:

-- ACCESS
mid('hello world', 7)

-- ASE, SQLDATAWAREHOUSE, SQLSERVER
substring('hello world', 7, 2147483647)

-- AURORA_MYSQL, AURORA_POSTGRES, COCKROACHDB, CUBRID, H2, HANA, HSQLDB, MARIADB, MEMSQL, MYSQL, POSTGRES, REDSHIFT, SYBASE, 
-- VERTICA
substring('hello world', 7)

-- DB2, DERBY, INFORMIX, ORACLE, SQLITE
substr('hello world', 7)

-- FIREBIRD, TERADATA
substring('hello world' FROM 7)

-- INGRES
substring('hello world', CAST(7 AS integer))

(These are currently generated with jOOQ 3.14, see #10141)

The jOOQ Logo