SUBSTRING

Description

Returns the subset of a string based on the specified start position. (RedShift SUBSTRING function).

Grammar Syntax

SUBSTRING(character_string FROM start_position [ FOR number_characters ] )

SUBSTRING(character_string, start_position, number_characters )

SUBSTRING(binary_expression, start_byte, number_bytes )

SUBSTRING(binary_expression, start_byte )

This function is partially supported in Snowflake.

Sample Source Patterns

Input Code:

Redshift

IN -> Redshift_01.sql

SELECT SUBSTRING('machine' FROM 3 for 2) AS machien_3_2, SUBSTRING('machine',1,4) AS machine_1_4;

SELECT SUBSTRING('12345'::varbyte, 2, 4) AS substring_varbyte;

Results

machien_3_2	machine_1_4
ch	mach

Output Code:

Snowflake

OUT -> Redshift_01.sql

SELECT SUBSTRING('machine', 3, 2) AS machien_3_2, SUBSTRING('machine',1,4) AS machine_1_4;

SELECT SUBSTRING('12345':: BINARY, 2, 4) !!!RESOLVE EWI!!! /*** SSC-EWI-RS0006 - THE BEHAVIOR OF THE SUBSTRING FUNCTION MAY DIFFER WHEN APPLIED TO BINARY DATA. ***/!!! AS substring_varbyte;

Results

machien_3_2	machine_1_4
ch	mach

Know Issues

When the start_position in Redshift is 0 or less, the SUBSTRING function performs a mathematical operation (start_position + number_characters). If the result is 0 or less, SUBSTRING returns an empty string. In Snowflake, the behavior for start_position when it is 0 or less differs, leading to different results.

Related EWIs

SSC-EWI-RS0006: The behavior of the SUBSTRING function may differ when applied to binary data.