SQL - LEFT() and RIGHT()
The LEFT() and RIGHT() functions extract substrings from text fields. LEFT() starts from the beginning, RIGHT() from the end. Both accept two parameters: the string and the number of characters to...
Key Insights
- LEFT() and RIGHT() extract a specified number of characters from the start or end of a string, essential for parsing fixed-width data, extracting codes, and manipulating identifiers
- These functions handle NULL values gracefully by returning NULL, but require careful consideration of string length to avoid extracting incomplete data
- Performance degrades when used in WHERE clauses on large tables; consider computed columns or full-text indexing for production queries involving these functions
Understanding LEFT() and RIGHT() Syntax
The LEFT() and RIGHT() functions extract substrings from text fields. LEFT() starts from the beginning, RIGHT() from the end. Both accept two parameters: the string and the number of characters to extract.
SELECT
LEFT('ApplicationArchitect', 11) AS first_part,
RIGHT('ApplicationArchitect', 9) AS last_part;
Result:
first_part | last_part
------------|----------
Application | Architect
The syntax is consistent across SQL Server, MySQL, and PostgreSQL. However, Oracle requires SUBSTR() instead:
-- Oracle equivalent
SELECT
SUBSTR('ApplicationArchitect', 1, 11) AS first_part,
SUBSTR('ApplicationArchitect', -9) AS last_part
FROM DUAL;
Extracting Fixed-Width Data
Legacy systems often store data in fixed-width formats. LEFT() and RIGHT() parse these efficiently without complex string manipulation.
CREATE TABLE legacy_records (
record_id INT PRIMARY KEY,
fixed_data VARCHAR(50)
);
INSERT INTO legacy_records VALUES
(1, 'CUST001234567890SMITH NY20231215'),
(2, 'CUST002345678901JONES CA20231216'),
(3, 'CUST003456789012WILLIAMS TX20231217');
SELECT
record_id,
LEFT(fixed_data, 4) AS record_type,
SUBSTRING(fixed_data, 5, 14) AS customer_id,
SUBSTRING(fixed_data, 19, 9) AS last_name,
SUBSTRING(fixed_data, 28, 2) AS state,
RIGHT(fixed_data, 8) AS transaction_date
FROM legacy_records;
Result:
record_id | record_type | customer_id | last_name | state | transaction_date
----------|-------------|----------------|-----------|-------|------------------
1 | CUST | 001234567890 | SMITH | NY | 20231215
2 | CUST | 002345678901 | JONES | CA | 20231216
3 | CUST | 003456789012 | WILLIAMS | TX | 20231217
Working with Product Codes and SKUs
Product identifiers often encode information in their structure. LEFT() and RIGHT() extract category codes, warehouse locations, or version numbers.
CREATE TABLE products (
sku VARCHAR(20),
description VARCHAR(100)
);
INSERT INTO products VALUES
('ELC-2024-WHT-L-001', 'Electronics Widget Large White'),
('CLT-2024-BLK-M-045', 'Clothing Item Medium Black'),
('FRN-2023-BRN-XL-128', 'Furniture Component XL Brown');
SELECT
sku,
LEFT(sku, 3) AS category_code,
SUBSTRING(sku, 5, 4) AS year_code,
RIGHT(sku, 3) AS sequence_number,
SUBSTRING(sku, 14, 1) AS size_code
FROM products;
Result:
sku | category_code | year_code | sequence_number | size_code
---------------------|---------------|-----------|-----------------|----------
ELC-2024-WHT-L-001 | ELC | 2024 | 001 | L
CLT-2024-BLK-M-045 | CLT | 2024 | 045 | M
FRN-2023-BRN-XL-128 | FRN | 2023 | 128 | X
Handling NULL and Edge Cases
LEFT() and RIGHT() return NULL when passed NULL input. When the requested length exceeds the string length, they return the entire string without error.
SELECT
LEFT(NULL, 5) AS null_input,
LEFT('ABC', 10) AS exceeds_length,
LEFT('', 5) AS empty_string,
RIGHT('XYZ', 0) AS zero_length;
Result:
null_input | exceeds_length | empty_string | zero_length
-----------|----------------|--------------|------------
NULL | ABC | |
Protect against NULL propagation with COALESCE():
CREATE TABLE customers (
customer_id INT,
phone VARCHAR(20)
);
INSERT INTO customers VALUES
(1, '555-123-4567'),
(2, NULL),
(3, '555-987-6543');
SELECT
customer_id,
RIGHT(COALESCE(phone, '000-000-0000'), 4) AS last_four_digits
FROM customers;
Result:
customer_id | last_four_digits
------------|------------------
1 | 4567
2 | 0000
3 | 6543
Filtering with LEFT() and RIGHT()
Use these functions in WHERE clauses to filter records based on string patterns. Be aware this prevents index usage on the filtered column.
CREATE TABLE transactions (
transaction_id VARCHAR(20),
amount DECIMAL(10,2),
created_date DATE
);
INSERT INTO transactions VALUES
('TXN-2024-001-A', 150.00, '2024-01-15'),
('TXN-2024-002-B', 275.50, '2024-01-16'),
('REF-2024-001-A', -50.00, '2024-01-17'),
('TXN-2024-003-C', 425.75, '2024-01-18');
-- Find all transactions (not refunds) from 2024
SELECT *
FROM transactions
WHERE LEFT(transaction_id, 3) = 'TXN'
AND SUBSTRING(transaction_id, 5, 4) = '2024';
Result:
transaction_id | amount | created_date
----------------|--------|-------------
TXN-2024-001-A | 150.00 | 2024-01-15
TXN-2024-002-B | 275.50 | 2024-01-16
TXN-2024-003-C | 425.75 | 2024-01-18
Performance Optimization Strategies
String functions in WHERE clauses force full table scans. For frequently queried patterns, create computed columns with indexes.
CREATE TABLE orders (
order_id VARCHAR(20) PRIMARY KEY,
order_data VARCHAR(500),
order_year AS (SUBSTRING(order_id, 5, 4)) PERSISTED,
order_type AS (LEFT(order_id, 3)) PERSISTED
);
CREATE INDEX idx_order_year ON orders(order_year);
CREATE INDEX idx_order_type ON orders(order_type);
-- This query now uses indexes
SELECT order_id, order_data
FROM orders
WHERE order_year = '2024'
AND order_type = 'ORD';
For SQL Server, computed columns marked PERSISTED are physically stored and can be indexed. MySQL and PostgreSQL require generated columns:
-- PostgreSQL syntax
CREATE TABLE orders (
order_id VARCHAR(20) PRIMARY KEY,
order_data VARCHAR(500),
order_year VARCHAR(4) GENERATED ALWAYS AS (SUBSTRING(order_id, 5, 4)) STORED,
order_type VARCHAR(3) GENERATED ALWAYS AS (LEFT(order_id, 3)) STORED
);
Combining with Other String Functions
LEFT() and RIGHT() work effectively with CONCAT(), TRIM(), and REPLACE() for complex transformations.
CREATE TABLE raw_data (
data_id INT,
raw_code VARCHAR(30)
);
INSERT INTO raw_data VALUES
(1, ' DEPT-001-SALES-2024 '),
(2, ' DEPT-002-MARKETING-2024 '),
(3, ' DEPT-003-IT-2024 ');
SELECT
data_id,
CONCAT(
LEFT(TRIM(raw_code), 8),
'-',
RIGHT(TRIM(raw_code), 4)
) AS formatted_code,
REPLACE(
SUBSTRING(TRIM(raw_code), 10,
LEN(TRIM(raw_code)) - 14),
'-', ''
) AS department
FROM raw_data;
Result:
data_id | formatted_code | department
--------|----------------|------------
1 | DEPT-001-2024 | SALES
2 | DEPT-002-2024 | MARKETING
3 | DEPT-003-2024 | IT
Data Masking for Security
RIGHT() masks sensitive data while preserving the last few characters for identification.
CREATE TABLE payment_methods (
customer_id INT,
card_number VARCHAR(19)
);
INSERT INTO payment_methods VALUES
(101, '4532-1234-5678-9010'),
(102, '5425-2345-6789-0123'),
(103, '3782-822463-10005');
SELECT
customer_id,
CONCAT(
REPLICATE('*', LEN(card_number) - 4),
RIGHT(card_number, 4)
) AS masked_card
FROM payment_methods;
Result:
customer_id | masked_card
------------|------------------
101 | ***************9010
102 | ***************0123
103 | **************10005
Validation and Data Quality Checks
Use LEFT() and RIGHT() to validate data formats and identify malformed records.
CREATE TABLE email_list (
email_id INT,
email_address VARCHAR(100)
);
INSERT INTO email_list VALUES
(1, 'user@example.com'),
(2, 'invalid.email'),
(3, 'another@test.org'),
(4, '@nodomain.com');
SELECT
email_id,
email_address,
CASE
WHEN LEFT(email_address, 1) = '@' THEN 'Missing local part'
WHEN RIGHT(email_address, 4) NOT IN ('.com', '.org', '.net') THEN 'Invalid TLD'
WHEN CHARINDEX('@', email_address) = 0 THEN 'Missing @ symbol'
ELSE 'Valid format'
END AS validation_status
FROM email_list;
Result:
email_id | email_address | validation_status
---------|--------------------|-----------------
1 | user@example.com | Valid format
2 | invalid.email | Missing @ symbol
3 | another@test.org | Valid format
4 | @nodomain.com | Missing local part
LEFT() and RIGHT() provide straightforward solutions for common string manipulation tasks. They excel at parsing structured data, extracting identifiers, and performing simple validations. For production systems with large datasets, always consider indexing strategies and avoid using these functions in WHERE clauses on high-volume tables.