? How to Compare Strings in SQL

In SQL, string comparison is done using various operators and functions depending on what exactly you want to compare or check.

1. Using = Operator

• Compares two strings for exact equality.

SELECT * FROM employees WHERE name = 'John';

• Returns rows where name is exactly 'John'.

2. Using LIKE Operator

• Used for pattern matching with wildcards.

• % matches zero or more characters.

• _ matches exactly one character.

SELECT * FROM employees WHERE name LIKE 'Jo%';  -- names starting with 'Jo'SELECT * FROM employees WHERE name LIKE '_ohn'; -- names like 'John', 'Bohn', etc.

3. Using ILIKE Operator (PostgreSQL only)

• Case-insensitive version of LIKE.

SELECT * FROM employees WHERE name ILIKE 'jo%';

4. Using STRCMP() Function (MySQL)

• Returns 0 if strings are equal.

• Returns <0 if first string < second string.

• Returns >0 if first string > second string.

SELECT STRCMP('apple', 'banana');  -- Returns negative because 'apple' < 'banana'SELECT STRCMP('apple', 'apple');   -- Returns 0 because strings are equal

5. Using CASE for Complex Comparison

SELECT name,       CASE           WHEN name = 'John' THEN 'Match'           ELSE 'No Match'       END AS match_statusFROM employees;

6. Collation and Case Sensitivity

• String comparison may be case-sensitive or case-insensitive depending on the database collation settings.

• Use functions like UPPER() or LOWER() to normalize case for comparison:

SELECT * FROM employees WHERE UPPER(name) = UPPER('john');

If you want, I can provide examples of string comparison in a specific SQL dialect or more advanced string functions!