Functions/Text & patterns/substring
Text & patterns Scalar

substring()

Extracts substring starting from character start up to the end of the string. If optional argument length is set, extracts a substring of length characters instead. Note that a start value of 1 refers to the first character of the string.

Arguments & returns

2 overloads

substring(string, start)

Scalar
Overload link stringstart Returns
#01 VARCHARBIGINT VARCHAR

substring(string, start, length)

Scalar
Overload link stringstartlength Returns
#02 VARCHARBIGINTBIGINT VARCHAR

Example

1 more ↓
substring example
SQL
Haybarn WASM 1.5.5-rc3 · In your browser

Engine example · Run to view results.

More examples

substring example 2
SQL
Haybarn WASM 1.5.5-rc3 · In your browser

Engine example · Run to view results.

In other engines

Snowflakeregexp_replace()

REGEXP_REPLACE(subject, pattern, replacement, 3, 1)
In DuckDB
SUBSTRING(subject, 1, 2) || REGEXP_REPLACE(SUBSTRING(subject, 3), pattern, replacement)

SQLGlot example ↗

Used in larger rewrites 4

These examples use substring as one part of a larger SQL translation.

Amazon Redshiftregexp_substr()

SELECT REGEXP_SUBSTR(abc, 'pattern(group)', 2) FROM table
In DuckDB
SELECT REGEXP_EXTRACT(SUBSTRING(abc, 2), 'pattern(group)') FROM "table"

SQLGlot example ↗

Apache Hivelocate()

LOCATE('a', x, 3)
In DuckDB
CASE WHEN STRPOS(SUBSTRING(x, 3), 'a') = 0 THEN 0 ELSE STRPOS(SUBSTRING(x, 3), 'a') + 3 - 1 END

SQLGlot example ↗

BigQueryregexp_extract()

SELECT REGEXP_EXTRACT(abc, 'pattern(group)', 2) FROM table
In DuckDB
SELECT REGEXP_EXTRACT(NULLIF(SUBSTRING(abc, 2), ''), 'pattern(group)', 1) FROM "table"

SQLGlot example ↗

Presto

DATE_PARSE(SUBSTR(x, 1, 10), '%Y-%m-%d')
In DuckDB
STRPTIME(SUBSTRING(x, 1, 10), '%Y-%m-%d')

SQLGlot example ↗