# Character Position in Endpoint

> URL patterns, character by character.

Canonical URL: <https://datadriven.io/problems/character_position_in_endpoint>

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

While debugging a routing issue, an engineer needs to locate where the letter 'a' first appears in each API endpoint string (case-insensitive). Show the endpoint and the character position, excluding endpoints that don't contain 'a' at all, ordered by call ID.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests string function knowledge, specifically POSITION/STRPOS and case-insensitive search. Interviewers use string manipulation questions to verify candidates know their database's built-in functions.

---

### Break down the requirements

#### Step 1: Find position of 'a' case-insensitively

Use `POSITION('a' IN LOWER(endpoint))` to find the first occurrence, normalizing case.

#### Step 2: Exclude endpoints without 'a'

`WHERE POSITION('a' IN LOWER(endpoint)) > 0` filters out endpoints that do not contain the letter.

#### Step 3: Order by call_id

`ORDER BY call_id` as specified in the prompt.

---

### The solution

**String position with case normalization**

```sql
SELECT endpoint, POSITION('a' IN LOWER(endpoint)) AS char_position
FROM api_calls
WHERE POSITION('a' IN LOWER(endpoint)) > 0
ORDER BY call_id
```

> **Cost Analysis**
>
> Full scan of 30M rows with string function evaluation per row. The POSITION function is O(n) per string where n is string length. No index helps here since the computation is row-level.

> **Common Pitfall**
>
> Forgetting to apply LOWER() would miss uppercase 'A' characters, violating the case-insensitive requirement. Always normalize case before string searches.

---

## Common follow-up questions

- What is the difference between POSITION, STRPOS, and CHARINDEX? _(Tests cross-database knowledge: POSITION is ANSI SQL, STRPOS is PostgreSQL, CHARINDEX is SQL Server.)_
- How would you find all positions of 'a', not just the first? _(Tests recursive CTEs or REGEXP_MATCHES for multi-match extraction.)_
- What if endpoints were URL-encoded? _(Tests awareness that %61 also represents 'a' and may need decoding.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/character_position_in_endpoint)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.