CODEWITHSUNDEEP
sqlsql-server-2008string-comparison
Mahender
Mahender

Reputation: 5694

comparing two strings in SQL Server

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

int returnval = STRCMP(str1, str2)

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

Upvotes: 17

Views: 157942

Answers (2)

Mad Max
Mad Max

Reputation: 11

I use this:

SELECT IIF(str1 = str2, 0, -1)

Upvotes: 1

gbn
gbn

Reputation: 432742

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

Upvotes: 43

Related Questions