Below are the string literal date formats that SQL Server supports for the datetime2 data type. These are the formats that we can provide when setting a datetime2 date from a string literal.
Continue readingA Quick Look at TO_ASCII() in PostgreSQL
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 readingOverview of the REPEAT() Function in PostgreSQL
We 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.
5 String Functions that Return Length in PostgreSQL
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 readingCreate a Multi-Dimensional Array from a Query in PostgreSQL
In PostgreSQL, we can use array constructors to create arrays that contain elements that we specify. We can populate the array from literal values, or we can let a query provide the values.
We can also create multi-dimensional arrays. When populating the array from a query, we can use multiple subqueries to create each sub-array. Therefore we can create a multi-dimensional array from a query.
Continue readingUsing REGEXP_SPLIT_TO_ARRAY() in PostgreSQL
In 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 readingHow REGEXP_SPLIT_TO_TABLE() Works in PostgreSQL
In 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 readingA Quick Look at PostgreSQL’s STARTS_WITH() Function
When 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 readingFix Error “cannot take logarithm of a negative number” in PostgreSQL
If you’re getting an error that reads “ERROR: cannot take logarithm of a negative number” when using either the log() function or log10() function in PostgreSQL, it’s probably because you’re passing a negative value to the function.
These functions require a value greater than zero. This is true even for the base argument of the log() function (the argument that specifies which base to use).
To fix this issue, be sure to pass a value greater than zero to these functions.
Continue readingCreate an Array from a Query in PostgreSQL
In PostgreSQL, an array constructor is an expression that builds an array value using values for its member elements.
One of the things we can do is build an array based on the results of a subquery.
Continue reading