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, BIGQUERY, COCKROACHDB, CUBRID, H2, HANA, HSQLDB, IGNITE, MARIADB, MEMSQL, MYSQL, POSTGRES, 
-- REDSHIFT, SNOWFLAKE, 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.15, see #10141)

The jOOQ User Manual : SQL building : Column expressions : String functions : SUBSTRING

previous : next