comparing two strings in SQL Server

sql sql-server-2008 string-comparison
Mahender picture Mahender · Jul 8, 2011 · Viewed 118.3k times · Source

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

gbn picture gbn · Jul 8, 2011

There is no direct string compare function in SQL Server

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 (added on edit)
END

Notes

  • you can wraps this via a UDF using CREATE FUNCTION etc
  • you may need NULL handling (in my code above, any NULL will report 1)
  • str1 and str2 will be column names or @variables

Related questions

How to Compare two strings using a if in a stored procedure in sql server 2008?

I want to do something like this: declare @temp as varchar set @temp='Measure' if(@temp == 'Measure') Select Measure from Measuretable else Select OtherMeasure from Measuretable

Read More
Cast collation of nvarchar variables in t-sql

I need to change the collation of an nvarchar variable. By documentation: (...) 3. The COLLATE clause can be specified at several levels. These include the following: Casting the collation of an expression. You can use the COLLATE clause to apply a …

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More