Category: SQL Server

SQL ISNULL() Explained

Posted on by Ian

Some RDBMSs provide an ISNULL() function that can be used when dealing with potentially null values.

MySQL, MariaDB, and Oracle Database each have an ISNULL() function that returns 1 if its argument is null, and 0 if it’s not.

SQL Server also has an ISNULL() function, but it works differently. It works more like how the IFNULL() function works in some other RDBMSs.

Other RDBMSs, such as PostgreSQL and SQLite don’t include an ISNULL() function, but they do support the IS NULL predicate (as do the other RDBMSs).

Continue reading →

Find All Non-Numeric Values in a Column in SQL

Posted on by Ian

If you ever encounter a character column that should be numeric, there’s always a possibility that it contains non-numeric data that you don’t know about.

For example, someone might have set up a Price column as a varchar column that should have been a numeric column, and now you need to clean up after them. You might start by identifying all non-numeric data so that you can work out what to do with it before converting the column to a numeric type.

In SQL, you can run a query to return non-numeric data from the column. The query you use will largely depend on your DBMS.

Continue reading →

Detect Whether a Value Contains at Least One Numerical Digit in SQL

Posted on by Ian

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.

Continue reading →

Fix “At least one of the arguments to COALESCE must be an expression that is not the NULL constant” in SQL Server

Posted on by Ian

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.

Continue reading →