PostgreSQL has an extensive collection of built-in string functions that allow us to manipulate text values. One task we might want to perform is to extract a substring from a larger string. Fortunately, PostgreSQL caters for our needs with at least two functions that make this a breeze.
string functions
A Quick Look at PostgreSQL’s REVERSE() Function
In PostgreSQL, we can use the reverse() function to return a specified string with its characters reversed. In other words, the string is returned with its characters in the reverse order to how we provided them.
Understanding PostgreSQL’s REGEXP_MATCHES() Function
In PostgreSQL, the regexp_matches() function returns substrings that match a given POSIX regular expression in a given string. We can specify that all matches are returned or just the first match. If all matches are returned, each is returned in a separate row.
We pass the string as the first argument and the pattern as the second argument. We can also provide a flag as an optional third argument, which determines how the function behaves.
Each returned row is a text array containing the whole matched substring or the substrings matching parenthesised subexpressions of the pattern.
How REGEXP_MATCH() Works in PostgreSQL
In PostgreSQL, the regexp_match() function returns a text array containing substrings that match a given POSIX regular expression in a given string. Only the first match is returned (to return all matches, use regexp_matches() instead).
We pass the string as the first argument and the pattern as the second argument. We can also provide a flag as an optional third argument, which determines how the function behaves.
A Quick Overview of PostgreSQL’s REGEXP_SUBSTR() Function
In PostgreSQL, we can use the regexp_substr() function to return a substring from a string based on a POSIX regular expression.
We can get the first occurrence or any other subsequent occurrence that matches the expression.
Difference Between QUOTE_LITERAL() and QUOTE_NULLABLE() in PostgreSQL
On the surface, PostgreSQL’s quote_literal() and quote_nullable() functions appear to do the same thing. But there is a difference.
It all comes down to how they deal with null arguments; quote_literal() returns null while quote_nullable() returns the string NULL.
Understanding the NORMALIZE() Function in PostgreSQL
PostgreSQL has a normalize() function that converts a string to the specified Unicode normalization form.
The function can only be used when the server encoding is UTF8.
How PostgreSQL’s CHARACTER_LENGTH() Function Works
In PostgreSQL, we can use the character_length() function to return the number of characters in a given string.
It accepts one argument; the string for which to return the length.
The character_length() function is a synonym for the char_length() function and so both do the same thing. We can also use the length() function to get the same result.
About PostgreSQL’s LENGTH() Function
In PostgreSQL the length() function returns the number of characters in a given string.
We pass the string as an argument and the function returns the number of characters as an integer.
A Quick Look at CHAR_LENGTH() in PostgreSQL
In PostgreSQL, the char_length() function returns the number of characters in a string. We pass the string as an argument and the function returns the number of characters in that string.
We can also use character_length() and length() to get the same result.