website logo
⌘K
🏠Homepage
👋Introduction
Key Concepts & Architecture
🚀Getting Started
👨‍💻SQL Reference
SQL Queries
SQL Clauses
SQL Data Types
SQL Functions
Schema
🚨Error Handling
🆚Differences Between Oxla vs. PostgreSQL
Docs powered by archbee 

ENDS_WITH

12min

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.

Syntax
|
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 a Table
|
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);

Display the Table
|
SELECT * FROM courses;


The above query will display the following table:

The courses 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 table
|
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.

Output
|
+----------------------------------+-------------+
| 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 a Table
|
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');

Display the Table
|
SELECT * FROM patients_data;

The patients_data Table
|
+----------------+---------------------+---------------+--------------+-------------+
| 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 table
|
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).

Output
|
+--------------+--------------+
| allergies    | starts_with  |
+--------------+--------------+
| null         | true         |
| null         | true         |
| penicillin   | true         |
| sulfonamide  | true         |
| peniccilin   | true         |
+--------------+--------------+




Updated 11 May 2023
Did this page help you?
Yes
No
PREVIOUS
STARTS_WITH
NEXT
CONCAT
Docs powered by archbee 
TABLE OF CONTENTS
Overview
Examples
#Case 1: ENDS_WITH() function using column
Case 2: ENDS_WITH() function with no specified argument

Dedicated to data analytics that provides 10x faster execution of analytical queries than current state of the art solutions. We are launching SOON! Join the waiting list for more info.




©2022 Oxla