comparing two strings in SQL Server

Question

Is there any way to compare two strings in SQL Server 2008 stored procedure like below?

int returnval = STRCMP(str1, str2)

• returns 0 if the strings are the same
• returns -1 if the first argument is smaller than the second according to the current sort order.
• returns 1 otherwise.

Above method I find in the MySQL but not in SQL Server.

Answer 1

There is no built-in string compare function in SQL Server, you have to do it manually:

CASE
    WHEN str1 = str2 THEN 0
    WHEN str1 < str2 THEN -1
    WHEN str1 > str2 THEN 1
    ELSE NULL -- one of the strings is NULL so won't compare
END

Notes:

• you can wrap this via a UDF using CREATE FUNCTION etc.
• you may need NULL handling in case one of the compared strings is NULL
• str1 and str2 will be column names or @variables