We can use the array_remove() function in PostgreSQL to remove elements from an array.
We pass two arguments when calling the function; the first is the array and the second argument is the element to remove. All occurrences of the specified element are removed.
The function returns an identical array, but without the specified element/s.
Continue readingIn PostgreSQL, the octet_length() function returns the number of bytes in a given string. We pass the string as an argument and the function returns the number of bytes in that string.
PostgreSQL has a bit_length() function that returns the number of bits in a given string. We pass the string as an argument and the function returns the number of bits in that string.
In PostgreSQL, we can use the array_cat() function to concatenate two arrays.
We pass both arrays as arguments when we call the function.
Continue readingPostgreSQL has a function called btrim() that we can use to trim both sides of a string. We can trim blank spaces or we can specify certain characters to trim.
It removes the longest string containing only characters from the ones we specify. If we don’t specify any characters, then it removes blank spaces from both sides.
We provide the string as the first argument, and the (optional) characters as the second.
Continue readingIn PostgreSQL, the array_prepend() function prepends an element to the start of an array.
The first argument is the element to prepend, and the second argument is the array.
Continue readingIn PostgreSQL, we can use the array_append() function to append an element to the end of an array.
The first argument is the array, and the second argument is the element to append.
Continue readingPostgreSQL has an array_positions() function that returns the position of the all occurrences of a given value in an array.
There’s also a array_position() function (singular) that returns the position of just the first occurrence. So to get just the first occurrence, use that function instead.
In PostgreSQL, we can use the string_to_table() function to return a set of rows, each containing a part of the string. The string is split based on the specified delimiter.
If we specify a null delimiter, then each character becomes a separate row in the output. If the delimiter string is empty, then the whole string is returned in a single row.
We also have the option of turning a specific substring into null if required.
In PostgreSQL we can use the cardinality() function to return the total number of elements in an array. This includes elements across all dimensions of the array.
We pass the array as an argument to the function, and it returns an integer of the total number of elements in that array.
Continue reading