Titles Starting with Vp — Pattern Matching in SQL

The problem

Helix Systems' HR system compiles a list of vice-president-level roles.

Write a query to return the ID and name of every employee whose title begins with VP.

Assumptions:

The employees table has one row per employee with an id, a name, and a title.
A qualifying employee has a title that starts with the literal characters VP at the very first position. Titles where VP appears later in the string do not qualify.

Output:

One row per qualifying employee, with columns id and name.

Schema · hr 4 tables

departments

id integer

name text

location text

budget numeric

salaries

id integer

employee_id integer

amount numeric

effective_date date

end_date? date

employees

id integer

name text

email text

department_id integer

manager_id? integer

hire_date date

title text

is_active boolean

job_history

id integer

employee_id integer

title text

department_id integer

start_date date

end_date? date

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  id,
  name
FROM
  employees
WHERE
  title ~ '^VP'

The shape

The caret in ^VP anchors the regex at position 1 of the string. The pattern matches only when the title begins with the literal characters VP. Without the anchor, the unanchored regex VP would also catch a title like Senior VP or VP-elect, which is not what the report wants.

Clause by clause

SELECT id, name returns the employee ID and name for the leadership roster.
FROM employees reads the employee table.
WHERE title ~ '^VP' keeps rows where the title starts with VP. The ^ is a position anchor, not a character; it does not consume any of the string. It asserts that the next characters in the pattern must appear at the very beginning. VP then matches the literal two characters V and P.

Why this and not `LIKE 'VP%'`

LIKE 'VP%' returns the same rows and is the more idiomatic spelling for a fixed prefix match. The regex form is shown here because the node is teaching regex anchors, and ^prefix is the regex equivalent of LIKE 'prefix%'. On a large table with a B-tree index, LIKE 'VP%' can use the index; the regex form generally cannot. For a real prefix-only filter, LIKE is usually the right call. For a more complex pattern that also needs to start at the beginning, the ^ anchor is how you say so.

The trap

The ^ must come first in the pattern. Writing 'V^P' is not "starts with V then P"; it matches a literal caret between V and P, which appears nowhere in the data. The anchor is a position assertion, and position-1 only exists at the very start of the pattern. Same rule for $ at the end.

You practiced ~ '^pattern' — anchor a regex at the start of the string so only matches at position 1 qualify.