Is there an equivalent in MS SQL Server to the <=> operator in MySQL?

Question

I was just reading this question, about <=> in MySQL:

What is this operator <=> in MySQL?

Is there an equivalent to this for SQL Server?

Answer 1

There's no anything like that built-in into SQL Server but you can use ISNULL, for example:

DECLARE @value1 AS VARCHAR(10) = 'a'
DECLARE @value2 AS VARCHAR(10) = NULL
DECLARE @nullreplace AS VARCHAR(10) = ''

SELECT CASE 
    WHEN ISNULL(@value1,@nullreplace) = ISNULL(@value2,@nullreplace) THEN 1
    ELSE 0
END

The problem with that is you need to select a value for NULL replacement that is not going to appear in the values you compare.

Answer 2

As was said, there's no direct operator. You might do something like this - replace

WHERE field <=> 'a' with

WHERE IsEqual(field,'a'), where IsEqual is a user function...

CREATE FUNCTION IsEqual( A VARCHAR(128), B VARCHAR(128) ) RETURNS BOOL
BEGIN
    IF (( A = B ) IS NULL) THEN
        RETURN ((A IS NULL) AND (B IS NULL));
    ELSE
        RETURN (A = B);
    END IF;
END