LEFT() and RIGHT() Functions in SQL

Introduction

LEFT and RIGHT are SQL string functions that return a requested number of characters from opposite ends of a text value. LEFT begins at the start of the string, while RIGHT begins at its end. They are concise tools for reading fixed-width prefixes and suffixes such as product categories, region codes, sequence numbers, initials, and the last few characters used in a masked display.

SELECT LEFT('DATABASE', 4) AS first_four,
       RIGHT('DATABASE', 4) AS last_four;

-- first_four: DATA
-- last_four:  BASE

The simple syntax can hide important design decisions. Extraction is reliable only when the position has a stable meaning. If a code contains a guaranteed four-character category and four-digit identifier, LEFT and RIGHT communicate that rule clearly. If components vary in length or are separated by delimiters, a delimiter-aware parser or separate columns is usually safer. Production use also requires attention to spaces, NULL, Unicode, collations, indexes, sensitive data, and database portability.

This tutorial develops a practical mental model and then connects it to real database work. The goal is not only to return characters, but to choose an extraction approach that remains correct when formats evolve, data becomes multilingual, tables grow, and multiple applications depend on the result.

Basic LEFT Syntax

LEFT accepts a source string and a character count. The count tells the database how many characters to return from the beginning.

LEFT(string_expression, number_of_characters)

The source can be a literal, column, parameter, concatenated value, or another compatible expression. In databases that support LEFT directly, the following query returns the first four characters.

SELECT LEFT('Software', 4) AS prefix;

-- Result: Soft

The second argument is a count, not a starting position. LEFT always begins at the left edge of the input.

Basic RIGHT Syntax

RIGHT uses the same two-argument pattern but counts characters from the end of the string.

RIGHT(string_expression, number_of_characters)
SELECT RIGHT('Software', 4) AS suffix;

-- Result: ware

RIGHT is particularly readable when a specification defines a fixed suffix. It does not search for a delimiter or infer a component boundary; it simply returns the requested number of characters from the end.

Understanding the Extraction Direction

For the value DATABASE, LEFT moves inward from the first character and RIGHT moves inward from the last. With a count of three, the results are DAT and ASE. Neither function changes the order of the extracted characters.

SELECT LEFT('DATABASE', 3) AS from_start,
       RIGHT('DATABASE', 3) AS from_end;

-- from_start: DAT
-- from_end:   ASE

This distinction sounds obvious, yet many defects come from confusing a count with a position. Use SUBSTRING when extraction must start at an arbitrary position in the middle.

Using LEFT and RIGHT with Columns

Functions become more useful when applied to table data. Suppose every product code follows ELEC-1001: four category characters, one hyphen, and four identifier characters.

SELECT product_code,
       LEFT(product_code, 4) AS category_code,
       RIGHT(product_code, 4) AS product_number
FROM products;
product_codecategory_codeproduct_number
ELEC-1001ELEC1001
BOOK-2001BOOK2001
FOOD-3001FOOD3001

The expression does not verify the format. A short value, missing hyphen, or unexpected prefix can still produce a result that looks plausible. Validation belongs at ingestion or in a database constraint when the code is an important contract.

Fixed-Width Prefixes

LEFT is appropriate when a prefix has a documented, fixed width. Employee values such as USA-1001, IND-1002, and CAN-1003 can expose a three-character country code with LEFT(employee_code, 3). The rule is easy for a reviewer to understand because the query mirrors the data specification.

SELECT employee_code,
       LEFT(employee_code, 3) AS country_code
FROM employees;

Do not assume that all real country, currency, department, or product identifiers have the same length unless the owning system guarantees it. A readable function cannot compensate for an undocumented format.

Fixed-Width Suffixes

RIGHT is the corresponding choice for a stable suffix. For employee codes EMP-1001, EMP-1002, and EMP-1003, the last four characters represent the employee number.

SELECT employee_code,
       RIGHT(employee_code, 4) AS employee_number_text
FROM employees;

The result remains text. If numeric ordering or arithmetic is required, validate the suffix and convert it with a DBMS-appropriate safe conversion function. A direct cast can fail when dirty values enter the table.

Extracting First and Last Characters

A count of one returns a single boundary character. This can support alphabetical grouping, simple classification, format diagnostics, or presentation logic.

SELECT LEFT('Database', 1) AS first_character,
       RIGHT('Database', 1) AS last_character;

-- first_character: D
-- last_character:  e

Character comparisons still follow the database collation. If the first character is used for grouping, decide whether uppercase and lowercase values, accents, and non-Latin text should share a group.

Generating Initials

LEFT can take the first character from separate name columns. Applying UPPER produces a consistent display for ordinary casing rules.

SELECT CONCAT(
           UPPER(LEFT(first_name, 1)),
           UPPER(LEFT(last_name, 1))
       ) AS initials
FROM employees;

This is presentation logic, not a universal model of personal names. Some people have one name, multiple family names, preferred initials, non-alphabetic characters, or scripts for which the concept differs. Allow the product requirements and user preference to control display behavior.

Trimming Before Extraction

Leading spaces affect LEFT and trailing spaces affect RIGHT. When boundary whitespace is accidental, apply TRIM before extracting.

SELECT LEFT(TRIM(product_code), 4) AS category_code,
       RIGHT(TRIM(product_code), 4) AS product_number
FROM products;

The order matters. LEFT(TRIM(value), 4) removes unwanted boundaries first, while trimming after extraction may leave an incomplete prefix. Confirm what TRIM removes in the target database, because whitespace can include more than the ordinary space character.

Combining with UPPER and LOWER

Case conversion can normalize an extracted component for display, comparison, or grouping. The outer function receives only the prefix or suffix returned by the inner function.

SELECT UPPER(LEFT(employee_name, 3)) AS name_prefix,
       LOWER(RIGHT(filename, 4)) AS normalized_suffix
FROM source_data;

For Michael, the first expression returns MIC. For REPORT.PDF, the second returns .pdf. Case conversion follows collation and Unicode rules, so international behavior should be tested on the production database.

Combining with REPLACE

REPLACE can remove a known separator before fixed-width extraction, although this changes character positions and should be used only when that transformation is intentional.

SELECT LEFT(REPLACE(product_code, '-', ''), 4) AS category_code
FROM products;

-- ELEC-1001 becomes ELEC1001, then LEFT returns ELEC.

If codes may contain more than one hyphen, removing every occurrence can merge components unexpectedly. Delimiter-aware parsing is clearer when the delimiter identifies structure rather than decoration.

Extracting from Text Dates

A fixed ISO-style text value such as 2026-09-26 can yield year and day portions with LEFT and RIGHT.

SELECT LEFT(date_text, 4) AS year_text,
       RIGHT(date_text, 2) AS day_text
FROM imported_records;

This is reasonable during import validation when the source is genuinely text. A proper DATE or TIMESTAMP column should be queried with date functions, not string positions. Date functions understand calendar components, database types, and formatting rules; string extraction does not validate that February 31 is impossible.

Phone and Account Masking

RIGHT is often used to expose only the final digits of a phone or account identifier.

SELECT CONCAT('XXX-XXX-', RIGHT(phone_number, 4)) AS masked_phone,
       CONCAT('************', RIGHT(account_number, 4)) AS masked_account
FROM customer_profile;

Masking a query result is only a display control. It does not protect the stored value, database logs, backups, exports, monitoring traces, or unauthorized queries. Sensitive data also requires suitable access control, encryption or tokenization, retention limits, and logging policies. Do not expose even the last four characters when the privacy model does not permit it.

File Extensions and Variable Length

RIGHT(filename, 4) can recognize .pdf, but extensions are not universally four characters. Files may end in .js, .jpeg, .docx, contain multiple dots, or have no extension. A hard-coded count is therefore a format rule, not a general file parser.

SELECT filename
FROM documents
WHERE LOWER(RIGHT(filename, 4)) = '.pdf';

Use position functions, regular expressions, application parsing, or a separately stored media type when the suffix varies. Security-sensitive upload validation must inspect actual content and approved MIME types rather than trusting a filename extension.

Delimiter-Based Extraction

When components have variable lengths, find the delimiter instead of counting fixed positions. Exact functions differ by DBMS, but the logical steps remain the same: locate the separator, calculate the portion before or after it, and handle a missing separator explicitly.

-- SQL Server-style example: text before the first hyphen
SELECT LEFT(code_value, CHARINDEX('-', code_value) - 1)
FROM source_codes
WHERE CHARINDEX('-', code_value) > 0;

The guard prevents a negative length when no hyphen exists. Data with escaping, repeated delimiters, quoted components, or nested structure needs a real parser rather than increasingly complicated string expressions.

Prefix Filtering

LEFT can express a prefix condition directly.

SELECT product_code, product_name
FROM products
WHERE LEFT(product_code, 4) = 'ELEC';

If the requirement is simply "starts with ELEC," a prefix LIKE predicate is often clearer and may allow a conventional index range scan.

SELECT product_code, product_name
FROM products
WHERE product_code LIKE 'ELEC%';

Actual behavior depends on the DBMS, collation, data type, statistics, and index definition. Compare execution plans on representative data instead of assuming either form is faster.

Suffix Filtering

RIGHT can test a known suffix, but suffix predicates are generally harder for a normal left-to-right B-tree index to support.

SELECT filename
FROM documents
WHERE RIGHT(LOWER(filename), 4) = '.pdf';

A pattern such as LIKE '%.pdf' also begins with an unknown prefix, so it usually cannot use an ordinary index as efficiently as LIKE 'ELEC%'. For frequent suffix searches, store the parsed suffix in a validated column, use a generated column and index, or adopt a search feature suited to the workload.

Expression and Generated-Column Indexes

Some databases support indexes on expressions such as LEFT(product_code, 4). Others can index a computed or generated column. This turns a repeated calculation into an access path while keeping the original code available.

-- Conceptual syntax; exact support varies by DBMS
ALTER TABLE products
ADD category_key VARCHAR(4)
    GENERATED ALWAYS AS (LEFT(product_code, 4));

CREATE INDEX ix_products_category_key
ON products (category_key);

The indexed expression, query expression, collation, and data type must align. Generated values also require a stable format; otherwise the index efficiently stores incorrect categories.

Grouping and Distinct Values

LEFT and RIGHT can summarize fixed components without changing stored data.

SELECT LEFT(product_code, 4) AS category_code,
       COUNT(*) AS product_count
FROM products
GROUP BY LEFT(product_code, 4)
ORDER BY category_code;

SELECT DISTINCT LEFT(product_code, 4) is useful for profiling candidate prefixes. Treat the result as evidence about current data, not proof of a business rule. Unexpected short values, spaces, NULL, or malformed codes should be reviewed separately.

Sorting Extracted Suffixes

Sorting by RIGHT uses the extracted value's data type, which is usually text. Numeric-looking strings sort lexically unless every value has equal zero-padded width.

SELECT product_code
FROM products
ORDER BY RIGHT(product_code, 4);

Values 2, 10, and 100 may not appear in numeric order when treated as strings. Validate and safely cast the suffix when numeric meaning is required, or store the numeric component in its own numeric column.

NULL and Empty Strings

LEFT and RIGHT normally propagate NULL. They do not automatically convert a missing value to empty text.

SELECT LEFT(NULL, 3) AS left_result,
       RIGHT(NULL, 3) AS right_result;

COALESCE can supply a fallback, but use it only when the business meaning allows NULL and empty text to be treated alike.

SELECT LEFT(COALESCE(product_code, ''), 4) AS safe_prefix
FROM products;

Oracle treats an empty string as NULL in ordinary SQL character semantics, while other databases commonly distinguish them. Portability tests should include both cases.

Counts Longer Than the Input

When the requested count exceeds the available length, commonly used implementations return the complete string.

SELECT LEFT('SQL', 10) AS left_value,
       RIGHT('SQL', 10) AS right_value;

-- Common result for both: SQL

This behavior can hide malformed short codes. If exactly four characters are required, validate the length separately instead of relying on extraction to signal an error.

Zero and Negative Counts

A zero count commonly returns an empty string in systems that implement LEFT and RIGHT. Negative counts are not portable: one database may reject them while another assigns special meaning.

SELECT LEFT('DATABASE', 0),
       RIGHT('DATABASE', 0);

Application-generated counts should be validated as non-negative integers before reaching SQL. Do not rely on a vendor-specific negative-count feature in code intended for multiple databases.

LEFT and RIGHT Versus SUBSTRING

LEFT is a convenience for extraction from the beginning. It is conceptually equivalent to a substring beginning at position one.

SELECT LEFT('DATABASE', 4),
       SUBSTRING('DATABASE', 1, 4);

-- Both return DATA in supporting DBMSs.

RIGHT is convenient for suffixes because a general substring expression may need the string length to calculate its starting position. SUBSTRING is more flexible for middle components and is often more portable, although its exact syntax still varies.

Comparison with Other String Functions

FunctionPrimary purpose
LEFTReturn characters from the beginning
RIGHTReturn characters from the end
SUBSTRINGReturn characters from a specified position
TRIMRemove supported boundary characters
REPLACEReplace matching text
UPPER / LOWERConvert letter case
CONCATCombine values into text

These functions are complementary. For example, UPPER(LEFT(TRIM(code), 4)) first trims the value, then extracts its prefix, and finally changes case.

MySQL Behavior

MySQL directly supports both functions.

SELECT LEFT('DATABASE', 4) AS prefix,
       RIGHT('DATABASE', 4) AS suffix;

Character set and collation influence string interpretation and comparisons. Test multi-byte input with the actual column type and connection configuration. Use EXPLAIN to check whether a functional predicate can use the planned index.

PostgreSQL Behavior

PostgreSQL supports LEFT and RIGHT and documents behavior for negative counts, but portable code should use non-negative counts with ordinary extraction meaning.

SELECT LEFT('DATABASE', 4) AS prefix,
       RIGHT('DATABASE', 4) AS suffix;

PostgreSQL can use expression indexes when a frequently queried expression is written consistently. Locale, collation provider, and database version are relevant for comparisons, although LEFT and RIGHT primarily determine character boundaries.

SQL Server Behavior

SQL Server supports LEFT and RIGHT for character and binary expressions, with documented conversion rules. Character types are the normal choice for textual extraction.

SELECT LEFT('DATABASE', 4) AS prefix,
       RIGHT('DATABASE', 4) AS suffix;

A persisted computed column can expose and index a recurring prefix or suffix. Collation controls comparison behavior, and supplementary-character-aware collations affect how some Unicode characters are counted.

Oracle Equivalent with SUBSTR

Oracle commonly uses SUBSTR rather than LEFT and RIGHT. Position one extracts from the beginning, while a negative starting position counts backward from the end.

SELECT SUBSTR('DATABASE', 1, 4) AS prefix,
       SUBSTR('DATABASE', -4) AS suffix
FROM dual;

-- prefix: DATA
-- suffix: BASE

Cross-database SQL should isolate vendor-specific expressions in views, repositories, or query adapters rather than scattering syntax decisions through application code.

Unicode and Character Semantics

A character is not always one byte. UTF encodings can use multiple bytes, and some user-perceived symbols contain multiple Unicode code points. Database functions follow the semantics defined by the product, data type, character set, collation, and version.

Test accented letters, non-Latin scripts, combining marks, emoji, and supplementary characters. A function that returns four database characters may not always return four user-perceived grapheme clusters. If the requirement concerns what a person sees on screen, application-level Unicode segmentation may be more appropriate.

Structured Data Belongs in Separate Columns

Repeatedly parsing USA-IT-1001 into country, department, and identifier can indicate a schema problem. Storing those attributes separately improves validation, indexing, joins, statistics, and reporting.

SELECT country_code,
       department_code,
       employee_id,
       CONCAT(country_code, '-', department_code, '-', employee_id)
           AS display_code
FROM employees;

A compound display code can be generated when needed. Keep a single encoded column only when it is an external identifier whose exact representation must be preserved, and document its grammar.

Username Generation

A simple convention can combine the first letter of a given name with a family name.

SELECT LOWER(CONCAT(LEFT(first_name, 1), last_name)) AS username_candidate
FROM employees;

This generates a candidate, not a guaranteed identity. Production logic must handle duplicate names, punctuation, spaces, Unicode, name changes, privacy, reserved words, maximum length, and uniqueness races. Preserve the user's name rather than overwriting it to satisfy a username format.

Data Profiling Before Adoption

Before making a prefix or suffix part of business logic, profile the source. Measure minimum and maximum lengths, count NULL and blank values, identify unexpected delimiters, and review distinct extracted components.

SELECT MIN(CHAR_LENGTH(product_code)) AS minimum_length,
       MAX(CHAR_LENGTH(product_code)) AS maximum_length,
       COUNT(*) AS total_rows
FROM products;

SELECT LEFT(product_code, 4) AS candidate_prefix,
       COUNT(*) AS row_count
FROM products
GROUP BY LEFT(product_code, 4)
ORDER BY row_count DESC;

Use the appropriate length function for the DBMS. Profiling converts an assumption about format into evidence and exposes legacy exceptions before they become production defects.

Defining a Format Contract

A positional expression should be backed by a written format contract. The contract identifies the owner of the code, allowed character set, exact or minimum length, prefix and suffix widths, separator rules, case rules, NULL policy, and versioning approach. Without those details, RIGHT(order_code, 6) is merely an unexplained assumption embedded in a query.

Consider a shipment number defined as a two-character region, a hyphen, an eight-digit sequence, another hyphen, and a one-character check indicator. The contract should state whether the region can expand, whether leading zeros are significant, and how malformed historical records are handled. Tests can then translate each rule into valid and invalid examples.

-- Validate a simplified fixed-width product format before extraction
SELECT product_code
FROM products
WHERE product_code IS NULL
   OR CHAR_LENGTH(TRIM(product_code)) <> 9
   OR SUBSTRING(TRIM(product_code), 5, 1) <> '-';

Syntax varies by database, but the principle is stable: validate the structure separately from extracting a convenient result. When the external owner changes the format, version the contract and migration rather than silently changing magic numbers.

Reusing Extraction Logic

Repeating the same LEFT and RIGHT expressions in SELECT, GROUP BY, ORDER BY, and application queries creates maintenance risk. A common table expression can calculate components once for a reporting query, while a view or generated column can centralize logic shared by multiple consumers.

WITH parsed_products AS (
    SELECT product_code,
           product_name,
           LEFT(TRIM(product_code), 4) AS category_code,
           RIGHT(TRIM(product_code), 4) AS product_number
    FROM products
)
SELECT category_code,
       COUNT(*) AS product_count
FROM parsed_products
GROUP BY category_code
ORDER BY category_code;

A CTE improves readability within one statement but does not automatically guarantee that the database computes the expression only once. Inspect the execution plan for performance. For site-wide contracts, place the rule behind an appropriately governed view, computed column, or data-access method and test that definition directly.

Migrating Compound Codes Safely

When a compound code has become difficult to query, a controlled migration can introduce typed component columns without breaking existing integrations. Begin by adding nullable columns, populate them from rows that pass format validation, and route malformed rows to an exception report. Compare reconstructed codes with originals before making the new columns authoritative.

-- Preview values before any update
SELECT employee_code,
       LEFT(employee_code, 3) AS proposed_country,
       RIGHT(employee_code, 4) AS proposed_number
FROM employees
WHERE employee_code IS NOT NULL;

After business owners resolve exceptions, backfill in restartable batches and add constraints or foreign keys that represent the real domains. Update application readers before writers, keep the original external code when audit or integration requirements need it, and monitor both representations during a transition period. A rollback plan should identify how new records written during migration will be reconciled.

The migration is complete only when reports, exports, APIs, and search paths use the typed attributes consistently. Removing repeated extraction is valuable, but preserving meaning and compatibility is more important than normalizing the schema quickly.

Monitoring Format Drift

Even a well-defined format can drift when upstream systems change, manual imports bypass validation, or a new business unit introduces longer identifiers. Add data-quality monitoring that counts invalid lengths, unexpected separators, unknown prefixes, nonnumeric suffixes, and extraction collisions. A sudden increase should trigger investigation before dashboards or routing logic become inaccurate.

SELECT LEFT(TRIM(product_code), 4) AS observed_prefix,
       COUNT(*) AS row_count
FROM products
WHERE product_code IS NOT NULL
GROUP BY LEFT(TRIM(product_code), 4)
HAVING LEFT(TRIM(product_code), 4) NOT IN ('ELEC', 'BOOK', 'FOOD');

In mature systems, compare metrics by source application and ingestion date. That makes it easier to locate the producer responsible for a format change. Monitoring should complement constraints, not replace them; constraints prevent known invalid states, while monitoring reveals trends and assumptions that the current rules do not cover.

End-to-End Reporting Example

Suppose a legacy sales table stores product code, customer phone, and import date text. A report needs category totals, a masked contact reference, and a year label. Extraction can meet the immediate reporting requirement while preserving the original values for audit.

SELECT LEFT(TRIM(product_code), 4) AS category_code,
       LEFT(import_date_text, 4) AS import_year,
       CONCAT('XXX-XXX-', RIGHT(TRIM(phone_number), 4)) AS phone_reference,
       COUNT(*) AS sale_count,
       SUM(sale_amount) AS total_amount
FROM legacy_sales
WHERE product_code IS NOT NULL
  AND CHAR_LENGTH(TRIM(product_code)) = 9
GROUP BY LEFT(TRIM(product_code), 4),
         LEFT(import_date_text, 4),
         CONCAT('XXX-XXX-', RIGHT(TRIM(phone_number), 4));

This query is suitable only after the input assumptions are checked. The masked phone is not a unique identifier, the text year is not a validated date, and grouping by a presentation value can combine unrelated customers. A production report might instead join typed dimensions and use an internal customer key while displaying the masked value only in the final output.

Testing Strategy

Test ordinary, shortest, longest, empty, NULL, whitespace-padded, malformed, and delimiter-free values. Include mixed case, digits, punctuation, international text, combining characters, and values whose requested count is zero or exceeds the input length. Verify negative-count handling only when vendor-specific behavior is intentionally used.

Performance tests should use realistic row counts and data distribution. Compare plans for functional predicates, LIKE prefix searches, generated columns, and expression indexes. Integration tests must confirm that the application, reports, exports, and downstream services interpret extracted components consistently.

Common Mistakes

Common errors include treating the character count as a starting position, using LEFT when the desired value is at the end, and forgetting that spaces participate in extraction. Other mistakes are assuming every suffix has the same length, parsing typed dates as text, sorting numeric suffixes lexically, and trusting a file extension as a security control.

At scale, applying a function to every row in a filter can prevent an ordinary index from being used effectively. At the design level, repeatedly extracting several components from one field often means the schema should store those components separately.

Best-Practice Checklist

  1. Use LEFT only when the prefix width and meaning are stable.
  2. Use RIGHT only when the suffix width and meaning are stable.
  3. Trim accidental boundary spaces before extraction.
  4. Use delimiter-aware parsing for variable-length components.
  5. Use date functions for DATE and TIMESTAMP columns.
  6. Keep structured attributes in separate typed columns when possible.
  7. Profile lengths and malformed values before adopting positional rules.
  8. Review execution plans for function-based predicates.
  9. Use generated columns or expression indexes for frequent searches.
  10. Test NULL, Unicode, collation, and DBMS edge cases.
  11. Treat masking as presentation, not complete data protection.
  12. Document fixed counts so they do not become unexplained magic numbers.

Interview Questions and Answers

What does LEFT do? It returns a specified number of characters from the beginning of a string.

What does RIGHT do? It returns a specified number of characters from the end of a string.

What does the second argument mean? It is the number of characters to return, not a starting position.

What happens with NULL? LEFT and RIGHT normally return NULL when the source is NULL.

What happens when the count is longer than the input? Common implementations return the available string, but target behavior should be verified.

How is LEFT different from SUBSTRING? LEFT always starts at the beginning; SUBSTRING can begin at a specified position.

Can these functions be used in WHERE? Yes, but applying a function to an indexed column can affect index use.

Why might LIKE be preferable for a prefix? A predicate such as code LIKE 'ELEC%' may be clearer and more index-friendly than LEFT(code, 4) = 'ELEC'.

Does Oracle use the same functions? Oracle commonly uses SUBSTR with positive or negative starting positions.

Are LEFT and RIGHT safe for variable formats? Only when a fixed count remains meaningful; otherwise use delimiters, typed columns, or a proper parser.

Key Takeaway

LEFT and RIGHT are readable functions for extracting stable, fixed-width text from the beginning or end of a value. Their reliability depends more on the data contract than on the syntax.

RELIABLE POSITIONAL EXTRACTION = DOCUMENTED FORMAT
                               + VALIDATED LENGTH
                               + CLEAN BOUNDARIES
                               + NULL AND UNICODE TESTING
                               + INDEX-AWARE QUERY DESIGN
                               + APPROPRIATE SCHEMA

Use LEFT for a known prefix and RIGHT for a known suffix. When the structure varies, locate a delimiter or store the components separately. Review portability and execution plans, and never mistake display masking or filename parsing for a complete security control.