Database Management Systems
In PostgreSQL, we can use the to_hex() function to convert a number to its equivalent hexadecimal representation.
The function accepts the number as an integer or bigint, and it returns the result as a text representation of its hexadecimal equivalent.
Continue readingTemporal tables in SQL Server provide a powerful way to track historical data changes. However, when it comes to clearing out data from these tables, the standard TRUNCATE statement doesn’t work the same as it does on normal (non-temporal) tables.
This article will guide you through the process of truncating a temporal table while maintaining its integrity.
Continue readingBy default, PostgreSQL arrays are one-based. This means that we need to use 1 if we want to reference the first element in the array, 2 for the second, and so on.
But we also have the option of specifying our own subscript range for an an array. For example we could create a zero-based array, a ten-based array, or even a negative value such as a negative ten-based array.
We can do this by using subscripted assignment to specify the actual subscript range for the array. Basically, we prefix the array with the subscript range, enclosed in square brackets, and an equals sign (=) between it and the array.
In PostgreSQL, we can use the ascii() function to get the numeric code of the first character of the argument.
When using UTF8, the function returns the Unicode code point of the character. Other multibyte encodings require an ASCII character.
Continue readingThe PostgreSQL split_part() function splits a string based on a specified delimiter and then returns the specified part from the split string.
The function accepts three arguments; the string, the delimiter, and the part that we want to return.
Continue readingIn PostgreSQL, we can use the pg_client_encoding() function to get the current client encoding name.
Temporal tables, introduced in SQL Server 2016, provide a powerful mechanism for tracking historical changes to data. This feature is particularly useful for auditing purposes, allowing organisations to maintain a complete history of data modifications without the need for complex triggers or custom logging solutions.
In this article, we’ll explore how to implement and use temporal tables for auditing in SQL Server, along with examples to demonstrate.
Continue readingIn PostgreSQL the right() function returns the specified number of rightmost characters in a given string.
We have the option of specifying the number of characters to return from the right or the number of characters to omit from the left. We do this by specifying a positive integer (to return n number of rightmost characters) or a negative integer (to return everything except n leftmost characters).
Continue readingIn PostgreSQL we can use the left() function to get the specified number of leftmost characters in a given string.
We have the option of specifying the number of characters to return from the left or the number of characters to omit from the right. We do this by specifying a positive integer (to return n number of leftmost characters) or a negative integer (to return everything except n rightmost characters).
Continue readingThe WHILE loop in SQL Server is a control-flow statement that allows us to repeatedly execute a block of code as long as a specified condition is true. It’s useful for iterative tasks and processing data in batches.