Cloud

ends_with

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 search reference. 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:

  • Returns NULL for the NULL record.

  • Returns true (including the NULL record) if the second_argument is not specified.

Examples

ends_with() function using column

Consider a table named courses:

CREATE TABLE courses (
  course_id int,
  course_name text,
  credits text
);
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 query displays the 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         |
+------------+----------------------------------+-----------+

This query checks which values of the course_name column end with “ology” in the preceding table:

SELECT course_name, ENDS_WITH(course_name, 'ology') FROM courses;

This returns 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        |
+----------------------------------+-------------+

ends_with() function with no specified argument

The patients_data table has a NULL value in the allergies column:

CREATE TABLE patients_data (
  record_number int,
  patient_name text,
  height_in_cm int,
  weight_in_kg int,
  allergies text
);
INSERT INTO patients_data
    (record_number, patient_name, height_in_cm, weight_in_kg, allergies)
VALUES
    (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');
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, run the ends_with function but with no specified second_argument:

SELECT allergies, ENDS_WITH(allergies, '') FROM patients_data;

The result shows that ends_with returns true for all records (even the NULL one):

+--------------+--------------+
| allergies    | ends_with    |
+--------------+--------------+
| null         | true         |
| null         | true         |
| penicillin   | true         |
| sulfonamide  | true         |
| peniccilin   | true         |
+--------------+--------------+