SUBSTRING() in SQL
Extracts a portion of a string based on specified position and length.
Syntax
Return type
VARCHAR (or equivalent string type in the respective DBMS)
SUBSTRING() Function Example
What is SUBSTRING() in SQL?
The SUBSTRING() function in SQL extracts a specific portion of a string based on a given starting position and an optional length. It is widely used for manipulating text data, allowing users to retrieve sections of strings for formatting, filtering, or analysis. The function is available in databases like MySQL, PostgreSQL, SQL Server, and SQLite, though syntax may vary slightly. If the length parameter is omitted in some databases, the function returns the rest of the string. SUBSTRING() is useful for extracting names, parsing email addresses, truncating text, and isolating relevant data from larger text fields.
Parameters:
string: The source string to extract from
start_position: Starting position (1-based index in most DBMSs)
length: Optional number of characters to extract
Example Use Cases:
-- Extract first 3 characters of a name
-- Extract year from date string
-- Extract domain from email
Notes:
Behavior: If start_position is negative, counts from end in some DBMSs
Performance Considerations: Efficient for standard string operations
Version Info: Core SQL function, supported in all major versions
Error Handling:
Error: Invalid start position or length may return empty string or error
Supported Databases:
DBMS
Function / Syntax
Example
Behavior with NULL
MySQL
SUBSTRING()
SUBSTRING('Hello', 1, 2)
Returns NULL
PostgreSQL
SUBSTRING()
SUBSTRING('Hello' FROM 1 FOR 2)
Returns NULL
SQL Server
SUBSTRING()
SUBSTRING('Hello', 1, 2)
Returns NULL
SQLite
SUBSTR()
SUBSTR('Hello', 1, 2)
Returns NULL
BigQuery
SUBSTR()
SUBSTR('Hello', 1, 2)
Returns NULL
Snowflake
SUBSTRING()
SUBSTRING('Hello', 1, 2)
Returns NULL
Athena
SUBSTR()
SUBSTR('Hello', 1, 2)
Returns NULL
Related Functions:
LEFT()
Extracts a specified number of characters from the beginning (left side) of a string.
RIGHT()
Extracts a specified number of characters from the end (right side) of a string.
CHARINDEX()
Returns the starting position of a substring within a string. Position is 1-based (first character is position 1).
LENGTH()/LEN()
Returns the number of characters in a string. Note: Function name varies by DBMS (LENGTH in most, LEN in SQL Server).
PATINDEX()
Returns the starting position of the first occurrence of a pattern in a string. Uses pattern matching with wildcards.
