In PostgreSQL, the regexp_instr() function returns the starting or ending position of the N‘th match of a POSIX regular expression pattern to a string. If there’s no match, it returns zero.
We pass the string and pattern as arguments. The function also accepts some optional arguments that allow us to be specific with how the function works.
In PostgreSQL, we can use the substring() function to extract the first substring matching the specified POSIX or SQL regular expression.
In PostgreSQL, the regexp_count() function returns the number of times a given POSIX regular expression pattern matches in a given string.
We pass the string and pattern as arguments. We can also pass an argument to specify where to start the search. Additionally, we also have the option of specifying a flag that changes the function’s behaviour.
In PostgreSQL, we can use the quote_literal() function to quote strings within a SQL statement string.
Any embedded single-quotes and backslashes are properly doubled.
In PostgreSQL, the quote_nullable() function returns a given string suitably quoted to be used as a string literal in a SQL statement string. It returns the actual string NULL if the argument is null.
Any embedded single-quotes and backslashes are properly doubled.
In PostgreSQL, we can use the overlay() function to replace a substring within a string. The function allows us to be very specific with regards to where in the string to start the operation and how much of it should be replaced.
PostgreSQL has a couple of functions that enable us to get the position or positions of a given value in an array. One of them – array_position() – returns the position of just the first occurrence (as an integer), while the other function – array_positions() – returns the position of all occurrences (as an array).
So these functions are aptly named – one is singular and the other is plural.
But this isn’t the only difference. The array_position() function allows us to specify a start position, while array_positions() doesn’t.
In PostgreSQL, the ltrim() function trims the left side 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.
In PostgreSQL, we can use the rtrim() function to trim the right side 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.
In PostgreSQL, the trim() function can be used to trim both sides, or a specified side, 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.