[sql] 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)
  • 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.

This question is related to sql sql-server-2008 string-comparison

The answer is


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

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server-2008

Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object How to Use Multiple Columns in Partition By And Ensure No Duplicate Row is Returned SQL Server : How to test if a string has only digit characters Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query Get last 30 day records from today date in SQL Server How to subtract 30 days from the current date using SQL Server Calculate time difference in minutes in SQL Server SQL Connection Error: System.Data.SqlClient.SqlException (0x80131904) SQL Server Service not available in service list after installation of SQL Server Management Studio How to delete large data of table in SQL without log?

Examples related to string-comparison

How do I compare two strings in python? How to compare the contents of two string objects in PowerShell String comparison in bash. [[: not found How do I compare version numbers in Python? Test if a string contains a word in PHP? Checking whether a string starts with XXXX comparing two strings in SQL Server Getting the closest string match How can I make SQL case sensitive string comparison on MySQL? String Comparison in Java