ENDS_WITH
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 theNULL
record. -
It will return
true
(including theNULL
record) if thesecond_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);
SELECT * FROM courses;
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 |
+--------------+--------------+