17

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mahender
  • 5,554
  • 7
  • 38
  • 54

1 Answers1

43

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
Ian Kemp
  • 28,293
  • 19
  • 112
  • 138
gbn
  • 422,506
  • 82
  • 585
  • 676
  • You will use the case statement or User defined function to compare the string in sql server. You can check SOUNDEX & DIFFERENCE string function for more information regarding string comparability. – Yogesh Bhadauirya Jul 08 '11 at 11:48
  • 1
    @Yogesh Bhadauirya: To clarify, SOUNDEX & DIFFERENCE don't compare alphabetically – gbn Jul 08 '11 at 11:52
  • You are right @gbn, but this is useful if OP knows something about string comparisons function which is useful to find similarity of two strings. – Yogesh Bhadauirya Jul 08 '11 at 12:19