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?
answer
- where counting starts
- FROM a start, FOR a count
- what POSITION reports on a miss
- zero minus one
- guard the no-space row
basics
~20 sSUBSTRING(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 sSQL 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
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.
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.
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.
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.