Most major RDBMSs support the COALESCE() operator, which returns the first non-null value from its list of arguments.
COALESCE() is a SQL-standard feature (it’s included in the ISO/IEC 9075 specification).
SQL Server
Detect Whether a Value Contains at Least One Numerical Digit in SQL
Sometimes you might need to search a database table for only those rows that contain at least one number in a given column.
Technically, numbers can be represented by words and other symbols, but here “number” means “numerical digit”.
Below are examples of how to find rows that contain at least one number in various SQL based DBMSs.
Fix “At least one of the arguments to COALESCE must be an expression that is not the NULL constant” in SQL Server
In SQL Server, if you’re getting error Msg 4127 that reads “At least one of the arguments to COALESCE must be an expression that is not the NULL constant”, it’s probably because all of your arguments to the COALESCE() expression are the NULL constant.
To fix this issue, make sure at least one argument is not the NULL constant.
SQL Server COALESCE() Explained
In SQL Server, the COALESCE() expression returns its first non-null argument.
The way it works is, we pass a list of arguments to the expression, it evaluates the arguments in order and returns the current value of the first expression that initially doesn’t evaluate to NULL.
How to Convert a Unix Timestamp to a Date/Time Value in SQL Server
In SQL Server, we can use the following method to return a date and time based on a given Unix timestamp.
The Unix timestamp (also known as Unix Epoch time, Unix time, or POSIX time) is the number of seconds that have elapsed since 00:00:00 Thursday, 1 January 1970, Coordinated Universal Time (UTC).
Fix “date is incompatible with int” in SQL Server when Adding to or Subtracting from a Date
If you’re getting error message “Msg 206” that reads “Operand type clash: date is incompatible with int” in SQL Server while trying to add to (or subtract from) a date, it’s probably because you’re trying to perform arithmetic between an integer and a date value.
To fix this issue, either change the date value to a datetime value or use the DATEADD() function.
How to Get Values That Don’t Contain Numbers in SQL Server
If you have a column in a SQL Server database table that contains character data, but some rows also contain numbers within that data, you can use the following technique to return just those rows that don’t contain numbers.
Numbers can be represented by words and other symbols, but for the purpose of this article, “number” means “numerical digit”. So we’re finding values that don’t contain any numerical digits.
3 Ways to Delete Duplicate Rows in SQL Server while Ignoring the Primary Key
The following examples use T-SQL to delete duplicate rows in SQL Server while ignoring the primary key or unique identifier column.
More specifically, the examples delete duplicate rows but keep one. So, given two identical rows, one is deleted and the other remains. This is often referred to as “de-duping” the table, “deduplication” of the table, etc.
5 Ways to Find Rows that Contain Uppercase Letters in SQL Server
Below are five options for returning rows that contain uppercase letters in SQL Server.