skip to content

questions

6

In SQL, how do you extract the text before the first space in full_name with SUBSTRING and POSITION, and what breaks for a one-word name?

level: juniorimportance: must knowfreq 52%

answer

  1. where counting starts
  2. FROM a start, FOR a count
  3. what POSITION reports on a miss
  4. zero minus one
  5. guard the no-space row

basics

~20 s

SUBSTRING(full_name FROM 1 FOR POSITION(' ' IN full_name) - 1) returns the text before the first space. A one-word name makes POSITION return 0, so the length is -1, which some engines reject and others turn into an empty result.

solid answer

~50 s

SQL string positions start at 1, so I write `SUBSTRING(full_name FROM 1 FOR POSITION(' ' IN full_name) - 1)`: take characters from the first one up to, but not including, the first space. `POSITION` returns the 1-based index of the match and 0 when there is none, and that is where one-word names break it. For `'Ann'` the length becomes -1. PostgreSQL and SQL Server raise an error, which fails the whole statement, while MySQL returns an empty string and Oracle's `SUBSTR` returns NULL, so there the first name silently disappears. I guard it with `CASE WHEN POSITION(' ' IN full_name) = 0 THEN full_name ELSE ... END`, and I trim the column first, because a leading space puts the match at position 1 and makes the result empty. The spelling varies too: SQL Server uses `CHARINDEX`, Oracle uses `INSTR`.

go deeper

for a junior

Write the expression from memory: positions count from 1, POSITION finds the space, and the count is that position minus one. Then say out loud what POSITION returns when there is no space.

for a middle

Explain the window semantics of start and count, why a start of 0 returns fewer characters on some engines, and why the rest-of-name form returns the whole string on a miss.

for a senior

Show that you think about the rows a split was not written for: one-word names, leading spaces, NULLs and several spaces. Silent blanks on some engines are worse than an error, so say how you would detect them.

for a principal

Name-splitting rules never fit every culture, so treat a split in a query as a stopgap. The lead's angle is whether to keep splitting at read time or store a split with the raw value retained.

## The two functions `SUBSTRING` and `POSITION` are the two functions most string splitting in SQL is built from. **Positions in SQL strings start at 1**, not 0: the first character of `'Thomas'` is at position 1. - `SUBSTRING(s FROM start FOR count)` returns `count` characters beginning at `start`, so `SUBSTRING('Thomas' FROM 2 FOR 3)` is `'hom'`. - `FOR` is optional. Without it you get everything from `start` to the end of the string. - `POSITION(x IN s)` returns the 1-based index where `x` first appears in `s`, or **0** when it does not appear. - Either function returns NULL when its string input is NULL, which is not the same result as a miss. ## Building the first-name extraction Take a customer import where `full_name` holds `'Ann Lee'`. The space is at position 4, and you want the three characters before it, so the count is the position minus one: ```sql SELECT SUBSTRING(full_name FROM 1 FOR POSITION(' ' IN full_name) - 1) AS first_name FROM customer_import; ``` On simple data this is right, and it is what a screening question expects you to write. The interesting part is the row it was not written for. ## What a one-word name does For `'Ann'` there is no space. `POSITION` returns 0, so the count becomes -1. Engines disagree about a negative count, and the disagreement decides whether you notice: | Engine | Result for a count of -1 | What you see | |---|---|---| | PostgreSQL | Error: `negative substring length not allowed` | The whole statement fails | | SQL Server | Error: invalid length parameter | The whole statement fails | | MySQL | Empty string | A blank first name, no warning | | Oracle (`SUBSTR`) | NULL | A NULL first name, no warning | The two silent engines are the more dangerous ones: the import finishes and the damage appears later in a report. The fix is the same everywhere, a guard that handles the miss before the arithmetic runs: ```sql SELECT CASE WHEN POSITION(' ' IN TRIM(full_name)) = 0 THEN TRIM(full_name) ELSE SUBSTRING(TRIM(full_name) FROM 1 FOR POSITION(' ' IN TRIM(full_name)) - 1) END AS first_name FROM customer_import; ``` `TRIM` matters here. A value such as `' Ann Lee'` has its first space at position 1, so an untrimmed version asks for zero characters and returns nothing useful. A NULL `full_name` passes through correctly: the `= 0` test is not true for NULL, so `CASE` falls to its `ELSE` branch, and `SUBSTRING` on NULL gives NULL. ## Start positions outside the string A computed start can also leave the string, and this is another place where results differ by engine. PostgreSQL and SQL Server treat start and count as a window over positions, including positions that do not exist. | Expression | PostgreSQL, SQL Server | MySQL | Oracle (`SUBSTR`) | |---|---|---|---| | Start 0, count 3, on `'abcdef'` | `'ab'` | Empty string | `'abc'` | | Start 10, count 2, on `'abcdef'` | Empty string | Empty string | NULL | The first row surprises people. The window covers positions 0, 1 and 2, and only 1 and 2 exist, so two characters come back. MySQL returns nothing for a start of 0, and Oracle treats 0 as 1. Going past the end is not an error on any of them; Oracle shows NULL only because it stores no empty string. A negative start is different again: MySQL and Oracle count back from the end of the string, so never let portable code compute one. ## Taking the rest of the name The second half of the split has a quieter bug: 1. `SUBSTRING(full_name FROM POSITION(' ' IN full_name) + 1)` starts just past the first space. 2. For `'Ann'`, `POSITION` returns 0, so the start is 1. 3. The whole string comes back with no error, and `last_name` now repeats the first name. So the guard belongs on both columns. A name with several spaces also needs a decision: `'Mary Ann Smith'` splits into `'Mary'` and `'Ann Smith'`, because `POSITION` finds only the first match. ## Portability - The `FROM ... FOR` form works on PostgreSQL and MySQL. SQL Server's `SUBSTRING` takes three comma-separated arguments, and Oracle offers `SUBSTR` rather than `SUBSTRING`. - `POSITION(x IN s)` works on PostgreSQL and MySQL. SQL Server uses `CHARINDEX(x, s)`, while Oracle and SQLite use `INSTR(s, x)`, with the arguments the other way round. - SQLite has `substr` as well, and like MySQL and Oracle it counts a negative start back from the end. - All of these agree on counting from 1 and on returning 0 for a miss, so the guard translates directly from one engine to another.

  • What does SUBSTRING('abcdef' FROM 0 FOR 3) return in SQL, and why is it not 'abc'?
    On PostgreSQL it is 'ab', and SQL Server's three-argument form agrees. Start and count define a window over positions 0, 1 and 2, and position 0 does not exist, so only two characters come back. MySQL returns an empty string for start 0, and Oracle's SUBSTR treats 0 as 1 and returns 'abc', so portable code must never compute a start of 0.
  • How do you take everything after the first space, and what happens to the same one-word name?
    SUBSTRING(full_name FROM POSITION(' ' IN full_name) + 1) starts just past the space. With no space POSITION returns 0, so the start is 1 and the whole name comes back. Nothing errors, so a last_name column silently repeats the first name. Add the same CASE guard, returning NULL when there is no second word.
  • What does POSITION return when full_name is NULL, and does the CASE guard still work?
    It returns NULL, not 0. The test POSITION(...) = 0 is then not true, so CASE falls to its ELSE branch, where SUBSTRING also receives NULL and returns NULL. The row keeps a NULL first name with no error, which is usually right: a missing name stays missing rather than becoming an empty string.

  • SQL string positions start at 0, the way array indexes do in most programming languages.
  • POSITION returns -1 when the search text is missing from a non-NULL string.
  • A negative SUBSTRING length fails with the same error on every engine.
  • Asking SUBSTRING for a start past the end of the string raises an out-of-range error.
  • A one-word name simply comes back whole from the FOR POSITION minus one form.
  • SUBSTRING with FROM and FOR and POSITION with IN run unchanged on every major engine.
open as a page

In SQL, why can COUNT(first_name || ' ' || middle_name) come in below the customer row count, and does CONCAT fix that on every engine?

level: middleimportance: must knowfreq 58%

basics

~20 s

With standard ||, one NULL operand makes the whole expression NULL, and COUNT skips NULL, so rows with no middle name drop out. CONCAT is no universal fix: PostgreSQL and SQL Server ignore NULLs, but MySQL's CONCAT returns NULL.

open as a page

In SQL, what does TRIM(LEADING '0' FROM code) return for '00120' and '000', and what does a bare TRIM(code) remove?

level: juniorimportance: should knowfreq 42%

basics

~20 s

TRIM(LEADING '0' FROM code) turns '00120' into '120' and '000' into an empty string, or NULL on Oracle. A bare TRIM(code) removes only space characters from both ends, leaving tabs, newlines and interior spaces in place.

open as a page

In SQL, why do CHAR_LENGTH and OCTET_LENGTH give different results for a customer name like 'José', and when do you use each?

level: middleimportance: should knowfreq 45%

basics

~20 s

CHAR_LENGTH counts characters and OCTET_LENGTH counts bytes. In UTF-8 the é in 'José' takes two bytes, so the results are 4 and 5. Use characters for rules people reason about, and bytes for storage or payload limits.

open as a page

After moving a customer import to another SQL engine, SELECT DISTINCT name returns fewer rows and ORDER BY changes; how does collation explain both, and how do you pin the behaviour?

level: seniorimportance: should knowfreq 38%

basics

~20 s

A collation defines which strings count as equal and how they sort. A case- or accent-insensitive default merges 'Ann' and 'ANN' under DISTINCT, and a different ruleset reorders ORDER BY. Declare the collation on the column or with COLLATE.

open as a page

As a lead on a customer data team, how do you decide whether SQL string cleaning belongs in each query, a generated column or the ingest pipeline?

level: principalimportance: should knowfreq 26%

basics

~20 s

Decide by how many consumers need one definition and whether the step loses data. Keep lossy, contextual cleaning in the pipeline with the raw value retained, deterministic row-level forms in generated columns, and display formatting in queries.

open as a page