Here are five options for dealing with error Msg 8134 “Divide by zero error encountered” in SQL Server.
Continue readingCategory: SQL Server
How NULLIF() Works in SQL Server
In SQL Server, the NULLIF() expression checks the value of two specified expressions. It returns a null value if they’re equal, otherwise it returns the first expression.
Fix Msg 529 “Explicit conversion from data type int to xml is not allowed” in SQL Server
If you’re getting SQL Server error Msg 529 that reads something like Explicit conversion from data type int to xml is not allowed, it’s probably because you’re trying to perform a data type conversion that’s not permitted.
SQL Server doesn’t allow certain conversions. If you try to perform such a conversion, you’ll get this error.
Continue readingFix Msg 8116 “Argument data type varchar is invalid for argument 1 of session_context function” in SQL Server
If you’re getting SQL Server error Msg 8116 with the message Argument data type varchar is invalid for argument 1 of session_context function, it’s because you’re passing the wrong data type to a function – in this case the SESSION_CONTEXT() function.
How to Check a Column’s Data Type in SQL
In SQL, the columns information schema view, which returns information about columns, is an ISO standard view that is supported by most of the major RDBMSs. You can use this view to get information about a column’s data type.
Most of the major RDBMs also provide other methods for getting column information.
Here are examples of getting the data type of a column in various SQL databases.
Continue readingFix Msg 8117 “Operand data type varchar is invalid for sum operator” in SQL Server
If you’re getting SQL Server error Msg 8117 with the message Operand data type varchar is invalid for sum operator, it’s because you’re passing the wrong data type to an operator or function.
In this case, the error indicates that we’re passing a string to the SUM() function. The SUM() function does not operate on strings. It only works on numeric types.
The same error (Msg 8117) can also occur in other contexts – it’s not limited to the SUM() function.
How to Detect if a Value Contains at Least One Number in SQL Server
Sometimes you might need to search a database table for only those rows that contain at least one number in a given column.
Strictly speaking, numbers can be represented by words and other symbols, but for the purpose of this article, “number” means “numerical digit”.
Below is an example of how to use T-SQL to find rows that contain at least one number in SQL Server.
Continue readingFind Non-Numeric Values in a Column in SQL Server
There may be occasions where you need to check a column for non-numeric values. For example, you discover that a column is a varchar column when it really should be a numeric column.
This is easily done in SQL Server with the ISNUMERIC() function.
Format a Phone Number in SQL Server (T-SQL)
Here are some examples of formatting phone numbers in SQL Server.
This includes examples of formatting numbers in E.164 format (for international numbers), prepending the country code and area code, as well as omitting leading zero from the country code when required.
Continue readingSUM() Function in SQL Server
In SQL Server, 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 the expression.
SUM() only works on numeric columns. Null values are ignored.