Skip to main content

ā€‹
Overview

The STARTS_WITH() function determines whether the first argument starts with a specified string in the second argument or not. 
STARTS_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: STARTS_WITH() function using column

Letā€™s say we have a table with the title petsData, as shown below. 
CREATE TABLE petsData (
  petid int,
  petname text,
  species text,
  breed text,
  sex text,
  age int
);
INSERT INTO petsData 
    (petid, petname, species, breed, sex, age) 
VALUES 
    (2021001,'Bartholomeow','cat','persian','m',2),
    (2021004,'Jack','dog','boston terrier','m',1),
    (2022001,'Jesse','hamster','dzungarian','m',1),
    (2022010,'Bella','dog','dobberman','f',3),
    (2022011,'June','cat','american shorthair','f',2);

SELECT * FROM petsData;
The above query will show the following table: 
+----------+--------------+----------+---------------------+------+-----+
| petid    | petname      | species  | breed               | sex  | age |
+----------+--------------+----------+---------------------+------+-----+
| 2021001  | Bartholomeow | cat      | persian             | m    | 2   |
| 2021004  | Jack         | dog      | boston terrier      | m    | 1   |
| 2022001  | Jesse        | hamster  | dzungarian          | m    | 1   |
| 2022010  | Bella        | dog      | dobberman           | f    | 3   |
| 2022011  | June         | cat      | american shorthair  | f    | 2   |
+----------+--------------+----------+---------------------+------+-----+
From the table above, we want to retrieve the values of petname column that start with ā€œJā€ by using the following query: 
SELECT petname, STARTS_WITH(petname, 'J') FROM petsData;
It will return true to the pet with a pet starting with the letter J. Otherwise, false. 
+--------------+---------------+
|   petname     | starts_with  |
+---------------+--------------+
| Bartholomeow  | false        |
| Jack          | true         |
| Jesse         | true         |
| Bella         | false        |
| June          | true         |
+---------------+--------------+

ā€‹
Case 2: STARTS_WITH() function with no specified argument

Here we have the petsData table with a NULL value in the breed column. 
CREATE TABLE petsData (
  petid int,
  petname text,
  species text,
  breed text,
  sex text,
  age int
);
INSERT INTO petsData 
    (petid, petname, species, breed, sex, age) 
VALUES 
    (2021001,'Bartholomeow','cat','persian','m',2),
    (2021004,'Jack','dog','boston terrier','m',1),
    (2022001,'Jesse','hamster','dzungarian','m',1),
    (2022010,'Bella','dog','dobberman','f',3),
    (2022011,'June','cat','american shorthair','f',2),
    (2022012,'Phoebe','gold fish','','f',1);

SELECT * FROM petsData;

+----------+--------------+------------+---------------------+------+------+
| petid    | petname      | species    | breed               | sex  | age  |
+----------+--------------+------------+---------------------+------+------+
| 2021001  | Bartholomeow | cat        | persian             | m    | 2    |
| 2021004  | Jack         | dog        | boston terrier      | m    | 1    |
| 2022001  | Jesse        | hamster    | dzungarian          | m    | 1    |
| 2022010  | Bella        | dog        | dobberman           | f    | 3    |
| 2022011  | June         | cat        | american shorthair  | f    | 2    |
| 2022012  | Phoebe       | gold fish  |                     | f    | 1    |
+----------+--------------+------------+---------------------+------+------+
For example, we run the STARTS_WITH function but with no specified second_argument: 
SELECT breed, STARTS_WITH(breed, '') FROM petsData;
We will have the following result where the STARTS_WITH will return true to all records (even the null one): 
+---------------------+--------------+
| breed               | starts_with  |
+---------------------+--------------+
| persian             | true         |
| boston terrier      | true         |
| dzungarian          | true         |
| dobberman           | true         |
| american shorthair  | true         |
| null                | true         |
+---------------------+--------------+