Most SQL databases have a handful of “ranking” functions that allow us to rank data. By “handful”, I mean there’s a common set of around six SQL rank functions that most of the major RDBMSs appear to support.
SQL rank functions allow us to assign ranks or row numbers to result sets.
Continue readingIn PostgreSQL, we can use the chr() function to return a character based on its code.
The code is provided as an integer argument, and the function returns the character that the code represents. When using UTF8 encoding the argument is treated as a Unicode code point, otherwise it must designate an ASCII character.
Continue readingPostgreSQL has a unistr() function that evaluates escaped Unicode characters in its argument and returns the result as text.
In PostgreSQL, gcd() is a mathematical function that returns the greatest common divisor. We pass two numbers and the function returns the largest positive number that divides both inputs with no remainder.
In PostgreSQL, lcm() is a mathematical function that returns the least common multiple. We pass two numbers and the function returns the smallest strictly positive number that is an integral multiple of both inputs.
PostgreSQL has a translate() function which replaces certain characters in a string with certain other characters.
We specify the characters in the string that we want to replace, as well as the characters that we want to replace them with. The function then performs the translation and returns the string with the replaced characters.
Continue readingIn 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 reading