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.
Continue readingTag: functions
A Quick Look at TRIM_ARRAY() in PostgreSQL
In PostgreSQL, we can use the trim_array() function to trim a given number of elements from the end of an array.
The first argument is the array, and the second is the number of elements to trim.
Continue readingUsing ARRAY_TO_STRING() in PostgreSQL
In PostgreSQL we can use the array_to_string() function to convert an array to a string. It converts each array element to its text representation (if needed), and then concatenates all elements using the given delimiter.
Any null values are omitted from the result, but we do have the option of replacing any null values with a given text value.
Continue readingAn Overview of UNNEST() in PostgreSQL
In PostgreSQL, we can use the unnest() function to expand an array into a set of rows. We pass the array as an argument, and the function returns each element on a separate row.
We can use the function on multi dimensional arrays, and it’s also possible to use it to unnest multiple arrays by including it in the FROM clause of a query.
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.
Continue readingA 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.
A Quick Look at PostgreSQL’s REPLACE() Function
In PostgreSQL, we can use the replace() function to replace a given substring within a string with another substring.
The function replaces all occurrences of the substring. If the substring doesn’t exist in the string, then the original string is returned unchanged.
Continue reading