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.
t-sql
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.
Fix 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.
Fix 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.
Find 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.
Fix Msg 8116 “Argument data type date is invalid for argument 1 of substring function” in SQL Server
If you’re getting SQL Server error Msg 8116 with text that reads Argument data type date is invalid for argument 1 of substring function, it’s because you’re passing the wrong data type to a function – in this case, the SUBSTRING() function.
You could also see the same error (Msg 8116) in many other contexts – it’s not limited to the SUBSTRING() function.
Fix Msg 8114 “Error converting data type varchar to numeric” in SQL Server
If you’re getting SQL Server error Msg 8114 that reads something like Error converting data type varchar to numeric, it’s probably because you’re trying to perform a data type conversion that fails due to the value not being able to be converted to the destination type.
It’s not because you can’t convert that type to the new type. It’s because of the value itself.
How TRY_CAST() Works in SQL Server
In SQL Server, an often used function is CAST(), which converts an expression of one data type to another. But if the cast doesn’t succeed, then it returns an error.
Enter TRY_CAST().
The TRY_CAST() function doesn’t return an error if the cast fails. Instead, it returns NULL.
There are some occasions however, where it will return an error.