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.

Ian Kemp - SO dead by AI greed · Accepted Answer · 2023-06-23 12:13:53Z

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

edited Jun 23, 2023 at 12:13

Ian Kemp - SO dead by AI greed

1

answered Jul 8, 2011 at 11:33

gbn

435k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Yogesh Bhadauirya Over a year ago

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.

gbn Over a year ago

@Yogesh Bhadauirya: To clarify, SOUNDEX & DIFFERENCE don't compare alphabetically

Yogesh Bhadauirya Over a year ago

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.

Mad Max · Accepted Answer · 2024-03-04 15:37:55Z

1

I use this:

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

edited Mar 4, 2024 at 15:37

answered Mar 4, 2024 at 15:32

Mad Max

112 bronze badges

1 Comment

Abra Over a year ago

I think you misunderstood the question. Your solution does not return 1 (one) when str1 is larger than str2 (according to the current sort order). Still, as per my understanding of how this website works, incorrect answers are still considered to be valid answers.

Collectives™ on Stack Overflow

comparing two strings in SQL Server

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related