In MariaDB, we can use the IF EXISTS clause of the DROP TABLE statement to check whether the table exists or not before dropping it.
Author: Ian
3 Ways to List all Stored Procedures in a PostgreSQL Database
Here are three options for listing out all the stored procedures in a Postgres database.
Continue readingSUM() Function in MySQL
In MySQL, the SUM() aggregate function returns the sum of a given expression.
It can also be used to return the sum of all distinct (unique) values in an expression.
Continue readingReturn Query Results as a Comma Separated List in Oracle
In Oracle, we can use the LISTAGG() function to convert our query results to a comma separated list.
So, instead of each value being output in a separate row, all values are output in a single row, separated by a comma (or some other delimiter of our choosing).
Continue readingFix “Arithmetic overflow error converting int to data type numeric” in SQL Server
If you’re receiving error Msg 8115, Level 16, Arithmetic overflow error converting int to data type numeric in SQL Server, it’s probably because you’re performing an operation that results in a data conversion error due to an out of range value.
This will often happen when you try to convert a number to a different data type, but it’s out of the accepted range for the new data type.
Continue readingFix “Arithmetic overflow error converting expression to data type int” in SQL Server
If you’re receiving error Msg 8115, Level 16, Arithmetic overflow error converting expression to data type int in SQL Server, it could be that you’re performing a calculation that results in an out of range value.
This can happen when you use a function such as SUM() on a column, and the calculation results in a value that’s outside the range of the column’s type.
Fix “Arithmetic overflow error converting IDENTITY to data type…” in SQL Server
If you’re getting error “Msg 8115, Level 16 Arithmetic overflow error converting IDENTITY to data type…” error in SQL Server, it’s probably because you’re trying to insert data into a table when its IDENTITY column has reached its data type’s limit.
An IDENTITY column automatically increments the value that’s inserted with each new row. If the value being inserted is out of the range of the column’s data type, then the above error will occur.
3 Ways to Get a Column’s Data Type in SQL Server (T-SQL)
GUIs like SSMS or Azure Data Studio make it easy to see a column’s data type. Usually it’s a simple matter of navigating to the column in the object explorer and you can see the data type right next to the column.
But if you’re using T-SQL, you’ll need to run a query.
Continue readingFormat a Number as a Percentage in SQL
There are several ways to format a number as a percentage in SQL, depending on the DBMS being used.
Here are examples of adding a percent sign to a number in some of the most popular DBMSs.
Continue readingHow to Change the Locale When Formatting Numbers in PostgreSQL
If you’re formatting a number in PostgreSQL, and you want to change the locale so that the correct group and decimal separators are used, here’s how you can do that.
Continue reading