How does the SQL version of LEFT, MID and RIGHT work?

LEFT(sku, 3) takes the first three characters, exactly as in Excel. SUBSTRING(sku FROM 4 FOR 2) starts at character 4 and takes two, which is MID(A2, 4, 2). RIGHT(sku, 4) takes the last four characters.

Why can SQL give a different result from LEFT, MID and RIGHT?

Positions start at 1 in both, but check your database: some functions are zero-based.

LEFT, MID and RIGHT in SQL

LEFT, MID and RIGHT pull part of a text value by position; SQL has the same LEFT and RIGHT functions and SUBSTRING in place of MID.

The Excel version

=LEFT(A2, 3), =MID(A2, 4, 2), =RIGHT(A2, 4)

The SQL equivalent

This example assumes: products(id bigint, sku text, name text). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT id,
       sku,
       LEFT(sku, 3) AS prefix,
       SUBSTRING(sku FROM 4 FOR 2) AS middle,
       RIGHT(sku, 4) AS suffix
FROM products
ORDER BY id;

How it works

LEFT(sku, 3) takes the first three characters, exactly as in Excel.
SUBSTRING(sku FROM 4 FOR 2) starts at character 4 and takes two, which is MID(A2, 4, 2).
RIGHT(sku, 4) takes the last four characters.

Where the results differ from Excel

Positions start at 1 in both, but check your database: some functions are zero-based.
A negative length in PostgreSQL LEFT or RIGHT counts from the other end, while Excel returns an error.
LEFT and RIGHT exist in PostgreSQL, MySQL and SQL Server; SQLite uses SUBSTR for all three.

Skip the query: ask in plain English

If you would rather not write SQL, type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the result matches how your spreadsheet defines the number.

Extract the first three characters of each SKU as a category prefix.
How many products share each SKU prefix?