postgreSQL – SUBSTRING Function
The SUBSTRING function is used to extract a substring from a given string. It allows you to extract a portion of a string based on a specified starting position and length.
The syntax of the SUBSTRING function is as follows:
SUBSTRING(string FROM start_position [FOR length])
where string is the input string from which you want to extract the substring,
and start_position is the 1-based index position in the input string where you want to start extracting the substring,
and length (optional) is the number of characters you want to extract. If omitted, the function will extract the substring from the starting position to the end of the string.
Here’s an example of how you might use the SUBSTRING function in a SQL query:
SELECT SUBSTRING('Hello, World!', 8) AS substring_result;
In this example, the result would be the string 'World!', as the function extracted the substring starting from position 8 in the input string.
You can also specify the length parameter to extract a specific number of characters:
SELECT SUBSTRING('Hello, World!', 8 FOR 5) AS substring_result;
In this case, the result would be 'World', as the function extracted 5 characters starting from position 8.
Remember that string positions are 1-based in PostgreSQL, meaning the first character of the string is at position 1.
The SUBSTRING function can also be used with regular expressions for more complex pattern-based extraction.