Some DBMSs have functions that allow us to format numbers as currency just by passing the appropriate format string. This converts the number to a string with the applicable currency symbol, group separator, and decimal point (if relevant).
Other DBMSs don’t make it that easy, and you need to do a bit of work first.
Below are examples of using SQL to format numbers as currency in some of the most popular DBMSs.
Continue readingSQL Server has three recovery models; simple, full, and bulk logged. Each database uses one of these settings.
Backup and restore operations occur within the context of the recovery model of the database
You can change the recovery model of a database by using the ALTER DATABASE statement along with the SET RECOVERY option.
In most cases, concatenating a string and a number in SQL is no different to concatenating two strings.
Most DBMSs will concatenate the string and number as if they were both strings. This is because the number is usually converted to a string before the concatenation operation.
Continue readingThere are several ways to show a list of databases in SQL Server. Probably the easiest way is to use the sp_databases stored procedure.
The sp_databases stored procedure is a kind of SQL Server equivalent to the SHOW DATABASES command that can be used with DBMSs such as MySQL and MariaDB. OK, maybe they aren’t strictly equivalents, but based on their ease of use, they could be viewed as such from a user’s perspective.
Most of the major DBMSs provide us with a way to find a string within a string using SQL. By this, I mean use a SQL query to find the position of a substring within a string.
There are several SQL functions that allow us to do this, including INSTR(), LOCATE(), POSITION(), and CHARINDEX(). The function you use will depend on your DBMS, and possibly whether or not you need to specify a starting position.
Most major RDBMSs provide ways for us to format numbers using SQL. Some provide only basic formatting capabilities, while others are more advanced.
Below are examples of formatting a number in some of the more popular RDBMSs.
Continue readingIf you’re trying to back up the transaction logs in a SQL Server or SQL Edge database, but you get an error that states BACKUP LOG cannot be performed because there is no current database backup, you’ll need to perform at least one full backup of the database before you attempt to back up the transaction logs.
Continue readingBelow are examples of adding a leading zero to a number in SQL, using various DBMSs.
Continue readingMany RDBMSs have an INSTR() function that enables us to find a substring within a string. Some (such as MySQL and MariaDB) also have a LOCATE() function and a POSITION() function (also supported by PostgreSQL), that do a similar thing.
SQL Server doesn’t have an INSTR() function. Nor does it have a LOCATE() or POSITION() function. But it does have the CHARINDEX() function that does the same thing.
SQL Server also has the PATINDEX() function, which does a similar job to CHARINDEX().
Some DBMSs have an LPAD() and RPAD() function which can be used to pad numbers with leading and trailing zeros.
SQL Server doesn’t have such a function. But that doesn’t prevent us from being able to pad numbers with leading/trailing zeros.
Continue reading