intro
Extract text using the SUBSTRING in PostgreSQL or SUBSTR. Set 1-based start positions, optional lengths, and leverage regex for advanced string parsing.
If you need to extract part of a string in PostgreSQL, you'll be using its substring functions. Specifically, you'll either use the SUBSTRING function or SUBSTR. So, what's the difference between the two, and how do they work? Let's find out!
What Is SUBSTRING in PostgreSQL?
In PostgreSQL, SUBSTRING is a function (with SQL-standard syntax) to get the portion of a given string. SUBSTRING also has a shorthand alias SUBSTR, so both do the same thing.
For example, these PostgreSQL string functions are handy when you want the first 5 characters of a string field or a slice of a string from one position to another.
How to Use the SUBSTRING PostgreSQL Function
You can use the SUBSTRING function with this format:
1
SUBSTRING(string FROM start FOR length)
Where:
Example:
1
SELECT SUBSTRING('PostgreSQL' FROM 1 FOR 4) AS sub;
2
-- Result: 'Post'
Equivalently, you can use it with this function-style syntax:
1
SUBSTRING(string, start, length)
For example:
1
SELECT SUBSTRING('PostgreSQL' FROM 1 FOR 4) AS sub;
2
-- Result: 'Post'
As you can see in a PostgreSQL client like DbVisualizer, both example returns the first 4 characters of the string 'PostgreSQL', starting at position 1. In PostgreSQL (as in standard SQL), string positions are 1-based, meaning the first character is position 1.
Note that the same result can be achieved with SUBSTR:
1
SELECT SUBSTR('PostgreSQL', 1, 4) AS sub;
2
-- Result: 'Post'
Again, the result will be “Post”:
SUBSTR is simply an alias for SUBSTRING in PostgreSQL, so you can use whichever you prefer.
Note: The length part in both syntax options is optional. If you omit it, the function will return everything from the start position to the end of the string. For example, SUBSTRING('Hello World' FROM 7) would give 'World' (from position 7 to the end).
Key Points About SUBSTRING in PostgreSQL
PostgreSQL SUBSTRING Examples
Now that you know what SUBSTRING is and how it works, you’re ready to see it in action in some examples.
Example 1: Basic usage
1
SELECT SUBSTRING('abcdef', 3, 2); -- start at 3rd character, length 2
The result will be ‘cd’:
Example 2: Omitting the Length Argument
1
SELECT SUBSTRING('abcdef', 4); -- only the start argument is provided
This time the result will be 'def' (from 4th char to end):
Example 3: Using the SUBSTR Alias
1
SELECT SUBSTR('abcdef', 4);
Again, the result will be the same as before ('def'):
Comparison with Other Databases
So, PostgreSQL is flexible by providing both syntaxes and an alias. The presence of SUBSTR in PostgreSQL is simply for convenience/compatibility (for those coming from Oracle or MySQL, for example).
Edge Cases and Tips of SUBSTRING
Summary
In PostgreSQL, use SUBSTRING (or the alias SUBSTR) to extract parts of a string. The usage is straightforward: provide the string, a 1-based start position, and an optional length. The function is standard and similar across databases, with minor differences in how negative or out-of-range positions are handled.
As shown here, utilizingSUBSTRING in PostgreSQL becomes easier with a visual database client like DbVisualizer. That tool is a fully-featured database client that supports multiple DBMS technologies, offers advanced query optimization capabilities, and can generate ERD-type schemas with a few clicks. Download it for free today!
