Whether searching for specific records, filtering the data, or performing various data manipulation tasks, understanding how SQL handles the string equals is crucial.
In this guide, we will delve into the SQL string equals by looking at the supported syntax, usage, and various examples for a better understanding.
SQL String Equals Syntax
There are various ways in which you can perform the string filtering. However, one of the most common and easy technique is using the WHERE clause.
The following shows the syntax of the clause:
FROM table_name
WHERE column_name = 'value';
In this case, the “column_name” represents the column that we want to compare while the value refers to the string literal in which we wish to compare it to.
This uses the equality operator which checks if the provided strings are equal.
Sample Data
Before we dive into the usage, let us set up a basic sample table that allows us to demonstrate the usage in a more practical way.
Consider the example table and sample data as shown in the following:
employee_id INT PRIMARY KEY AUTO_INCREMENT,
employee_name VARCHAR(100),
department_id INT
);
INSERT INTO employees (employee_name, department_id) VALUES
('Phelps Dane', 101),
('Alana L', 102),
('E Johnson', 103),
('Daniel B', 104),
('Break Davis', 105),
('Jessica Wilson', 106),
('Daniel Garcia', 107),
('Karen Martinez', 108),
('Anthony Robinson', 109),
('Sophia Clark', 110);
This should create a table and add the sample records into the table.
Example 1: Basic Usage
Consider the following example query that uses the string equals operator to search for a matching record where the employee’s name is equal to Karen Martinez.
FROM employees
WHERE employee_name = 'Karen Martinez';
In the given query, we select all columns from the “employees” table where the “employee_name” column is equal to “Karen Martinez”.
The resulting output is as follows:
-----------+--------------+-------------+
8|Karen Martinez| 108|
Example 2: Case-Sensitive String Comparison
Although this may depend on the database system, by default, the string comparison in most database systems is case-insensitive.
To explicitly force the database to perform a case sensitive comparison, you can use the BINARY keyword as follows:
FROM employees
WHERE BINARY employee_name = 'Karen martinez';
In this case, since the provided string contains the lowercase M in the name Martinez, the database will treat it as a different string. Hence, there is no result.
Example 3: Using the Wildcard Characters
In some other cases, we may want to perform a string equality check using the wildcard characters. This matches the strings that match a specific format rather than a single string literal.
This can help to provide flexibility in the searches without actually overwhelming the database engine.
For example, we can use the wildcards like “%” (matches any sequence of characters) and “_” (matches any single character) in conjunction with string equals.
Let us look at an example as follows:
FROM employees e
WHERE employee_name LIKE 'A%';
This query retrieves all employees whose names start with the letter “A”.
Example 4: Escape the Special Characters
In a case where you need to search for certain characters as part of the string, you need to escape them when using them in string equals.
For example, if you want to search a character such as backslash or a single quote as part of the string, you need to escape it as follows:
FROM products
WHERE product_name = 'Sweet\'s Apples';
In this case, we use a backslash to escape the single quote within the string.
To escape a backslash, you can use the double backslashes as follows:
FROM products
WHERE product_name = 'Sweet\\ Apples';
This should include a backslash in the string literal.
Conclusion
In this tutorial, we learned about one of the most common and useful string operations in databases which is the string equality comparison. We learned the syntax and usage of the various operators to compare the strings.
