MySQL STRCMP() Function

The STRCMP() function in MySQL compares two strings and returns an integer indicating the result of the comparison.

Syntax

STRCMP(string1, string2)

Parameters

Definition and Usage

• The STRCMP() function compares string1 with string2.
• The function returns:
  • 0 if the two strings are equal.
  • -1 if string1 is lexicographically less than string2.
  • 1 if string1 is lexicographically greater than string2.

Return Values

• 0: When the two strings are equal.
• -1: When the first string is less than the second string (alphabetically or based on ASCII value).
• 1: When the first string is greater than the second string.

Usage Examples

Example 1: Compare two identical strings

SELECT STRCMP("SQL Tutorial", "SQL Tutorial");

Output:

Explanation: The two strings are exactly the same, so the result is 0.

Example 2: Compare two different strings

SELECT STRCMP("SQL Tutorial", "HTML Tutorial");

Output:

Explanation: "SQL Tutorial" is lexicographically greater than "HTML Tutorial" based on their ASCII values, so the result is 1.

Example 3: Compare string and a different string

SELECT STRCMP("Apple", "Banana");

Output:

Explanation: "Apple" is lexicographically less than "Banana", so the result is -1.

Technical Details

• Works In: From MySQL 4.0 onwards.
• Return Type: Integer (0, 1, or -1).
• Comparison Logic: The comparison is case-sensitive. The function compares strings based on their character order in the character set (e.g., ASCII or UTF-8).

Applications

1. Sorting and Ordering: Use STRCMP() to sort data in a custom order by comparing strings.
2. Conditional Checks: It can be used in IF statements to check if two strings are equal, greater, or smaller.
3. String Comparison: Useful in scenarios where strings need to be compared directly, such as validating user input, filtering results, or comparing search queries.

Key Notes

• The comparison is case-sensitive, meaning "apple" and "Apple" would not be considered equal (returning -1 and 1 respectively depending on the order).
• This function compares the strings lexicographically, similar to how words are ordered in a dictionary.