How to compare two strings in SQL

Problem

How to compare two strings in SQL?

Sample Data

Let's assume we have a table named Students with the following structure and data:

CREATE TABLE Students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    surname VARCHAR(100)
);

INSERT INTO Students (id, name, surname)
VALUES
(1, 'John', 'Doe'),
(2, 'Jane', 'Doe'),
(3, 'John', 'Smith');

MySQL Solution

SELECT * FROM Students WHERE name = 'John' AND surname = 'Doe';

Explanation (MySQL)

In the MySQL solution, we use the = operator to compare two strings. The = operator compares the string on its left with the string on its right and returns TRUE if they are equal and FALSE otherwise. In the above query, we are retrieving records from the Students table where the name is 'John' and surname is 'Doe'.

PostgreSQL Solution

SELECT * FROM Students WHERE name = 'John' AND surname = 'Doe';

Explanation (PostgreSQL)

In the PostgreSQL solution, we also use the = operator for string comparisons. The operation compares the string on its left with the string on its right and returns TRUE if they match and FALSE otherwise. The query above fetches records from Students table where the name is 'John' and surname is 'Doe'.

In both MySQL and PostgreSQL, string comparisons are case-sensitive. 'John' is not the same as 'john'. If you want to perform case-insensitive comparisons, you can use the LOWER() or UPPER() functions to convert both strings to the same case before comparing them.