In PostgreSQL, we can use the to_ascii() function to convert a string to ASCII from another encoding. This usually involves dropping any accents (diacritic signs) from the original string.
We pass the string as an argument to the function. We also have the option of specifying the encoding (either its name or its number).
Continue readingWe can use PostgreSQL’s repeat() function to repeat a string multiple times. We pass the string to the function, along with an integer that specifies how many times we want it repeated, and it returns the string repeated that many times.
PostgreSQL provides us with a handful of string functions that return the length of a given string.
But the result between these functions can be different, depending on which function we use. That’s because the “length” can be different, depending on what we’re measuring. Are we talking about the number of characters in the string? Or the number of bytes in the string? Or perhaps we want to know the number of bits in the string.
The function we use will depend on which of the above we’re trying to measure. Below are five functions that cater for each of the above questions.
Continue readingIn PostgreSQL, the regexp_split_to_array() function splits a string using a POSIX regular expression as the delimiter, and returns the result in a text array.
So we use a POSIX regular expression to specify the delimiter/s, and split the string based on that.
We pass the string as the first argument and the pattern as the second. We can also specify a flag to determine how the function behaves.
Continue readingIn PostgreSQL, the regexp_split_to_table() function splits a string using a POSIX regular expression as the delimiter, and returns the result in a table.
So we use a POSIX regular expression to specify the delimiter/s, and split the string based on that.
We pass the string as the first argument and the pattern as the second. We can also specify a flag to change the behaviour of the function.
Continue readingWhen using PostgreSQL, we can use the starts_with() function to check whether or not a string starts with a given substring.
We pass the string as the first argument, and the substring as the second.
It returns true if the string does start with the substring, otherwise it returns false.
Continue readingPostgreSQL has the SOME() construct that we can use to perform searches against arrays. It returns a Boolean result, and so we can incorporate this into our searches to return just those rows that yield true or false.
SOME() is a synonym for ANY(), and so we can use either one to get the same result.
In PostgreSQL, log10() is a mathematical function that returns the base 10 logarithm of its argument.
It was added in PostgreSQL 12 as a SQL-standard equivalent of the existing log() function.
There is a difference between the two functions though, because log() allows us to specify which base to use (although it defaults to base 10), whereas log10() uses base 10 only.
In PostgreSQL, we can use the trim_scale() function to remove any trailing zeros from the end of the fractional part.
This doesn’t change the value, it merely reduces the number characters whenever there are unnecessary zeros on the end.
Continue readingIn PostgreSQL, min_scale() is a mathematical function that returns the minimum scale needed to represent the supplied value precisely.
The “scale” is the number of fractional decimal digits. For example, a value of 1.9500 has a minimum scale of 2, whereas 1.957 has a minimum scale of 3.
We pass the value to the function as a numeric argument, and it returns the result as an integer.