StringsPostgreSQL only

STARTS_WITH

Checks whether a string starts with the given prefix.

Syntax

STARTS_WITH(<text>, <prefix>)
<text> ^@ <prefix>

Returns true or false. It does the same as LIKE '<prefix>%', but you don't have to think about % and _ in the prefix itself: it's compared literally. The ^@ operator is a shorter way to write the same thing.

Data for the examples

employees

name	department	salary	bonus	phone
Emma	backend	1200	300	+44 7700 900123
Liam	backend	1000	NULL	NULL
Olivia	frontend	1350	150	+44 7700 900456
Noah	frontend	950	NULL	+44 7700 900789
Sophia	design	1200	200	NULL

These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.

Does it start with the prefix

SELECT STARTS_WITH('PostgreSQL', 'Post') AS post,

STARTS_WITH('PostgreSQL', 'post') AS lowercase;

post	lowercase
true	false

Phone numbers with the +44 code

SELECT name, phone

FROM employees

WHERE STARTS_WITH(phone, '+44');

name	phone
Emma	+44 7700 900123
Olivia	+44 7700 900456
Noah	+44 7700 900789

When the prefix contains _

SELECT STARTS_WITH('userX1', 'user_') AS starts_with,

'userX1' LIKE 'user_%' AS like_matches;

starts_with	like_matches
false	true

In LIKE, _ means “any character”, so userX1 matches. STARTS_WITH compares the prefix literally.

Watch out

Case matters: STARTS_WITH('Emma', 'em') gives false. To ignore case — STARTS_WITH(LOWER(name), 'em').

For NULL the result is NULL, so in WHERE such a row simply won't pass.

Related functions

LEFT POSITION LOWER