This article will guide you on how to use the ENDS_WITH() function with its syntax and examples.
Overview
The ENDS_WITH() function determines whether the first argument ends with a specified string in the second argument or not.
ENDS_WITH(first_argument, 'second_argument')
first_argument: the specified argument, which will be the search reference. It can be a string or a column name.
second_argument: the specified argument, which will have the search keywords.
The input type will be STRING, and the return type is BOOL, shown as true or false.
💡Special case:
It will return NULL for the NULL record.
It will return true (including the NULL record) if the second_argument is not specified.
Examples
#Case 1: ENDS_WITH() function using column
Let’s say we have a table named courses:
CREATE TABLE courses (
course_id int,
course_name string,
credits string
);
INSERT INTO courses
(course_id, course_name, credits)
VALUES
(2111,'Basics of Plant Biotechnology',2),
(2102,'Biochemistry',3),
(1241,'Statistics',3),
(4142,'Microbial Biodiversity',2),
(3262,'Introduction to Plant Pathology',3),
(3233,'Enzyme Technology',2),
(1201,'Rural Sociology',2);
The above query will display the following table:
+
| course_id | course_name | credits |
+
| 2111 | Basics of Plant Biotechnology | 2 |
| 2102 | Biochemistry | 3 |
| 1241 | Statistics | 3 |
| 4142 | Microbial Biodiversity | 2 |
| 3262 | Introduction to Plant Pathology | 3 |
| 3233 | Enzyme Technology | 2 |
| 1201 | Rural Sociology | 2 |
+
Using the following query, we want to confirm the values of the course_name column that end with "ology" in the table above:
SELECT course_name, ENDS_WITH(course_name, 'ology') FROM courses;
It will return true to all the courses with the name ending with ology. Otherwise, false.
+
| course_name | ends_with |
+
| Basics of Plant Biotechnology | true |
| Biochemistry | false |
| Statistics | false |
| Microbial Biodiversity | false |
| Introduction to Plant Pathology | true |
| Enzyme Technology | true |
| Rural Sociology | true |
+
Case 2: ENDS_WITH() function with no specified argument
Here we have the patients_data table with a NULL value in the allergies column.
CREATE TABLE patients_data (
record_number int,
patient_name string,
height_in_cm int,
weight_in_kg int,
allergies string
);
INSERT INTO patients_data
(record_number, patient_name, height_in_cm, weight_in_kg, allergies)
VALUES
(2009000908,'Vivienne Desjardin',168,49,''),
(2012000876,'Elizabeth Reinhard',163,55,''),
(2015000965,'James McCarthy',188,70,'penicillin'),
(2020000109,'Jose Ramirez',170,70,'sulfonamide'),
(2020000222,'Stefani Ricci',170,70,'peniccilin');
SELECT * FROM patients_data;
+
| record_number | patient_name | height_in_cm | weight_in_kg | allergies |
+
| 2009000908 | Vivienne Desjardin | 168 | 49 | null |
| 2012000876 | Elizabeth Reinhard | 163 | 55 | null |
| 2015000965 | James McCarthy | 188 | 70 | penicillin |
| 2020000109 | Jose Ramirez | 170 | 70 | sulfonamide |
| 2020000222 | Stefani Ricci | 170 | 70 | peniccilin |
+
For example, we run the ENDS_WITH function but with no specified second_argument.
SELECT allergies, ENDS_WITH(allergies, '') FROM patients_data;
We will have the result where the ENDS_WITH will return true to all records (even the null one).
+
| allergies | starts_with |
+
| null | true |
| null | true |
| penicillin | true |
| sulfonamide | true |
| peniccilin | true |
+
