SQL Substring: Syntax, Usage, and Examples

The SQL substring function extracts part of a string based on starting position and length. Use it when you need to isolate specific characters in a column, whether it’s the first few letters of a name, a date fragment, or a code segment. Substring SQL queries are especially useful for cleaning, transforming, and analyzing text data.

How to Use SQL Substring

The basic syntax of the SQL substring function looks like this:

SUBSTRING(string_expression FROM start_position FOR length)

In some dialects like MySQL or SQL Server, you’ll often see:

SUBSTRING(string_expression, start_position, length)

Let’s break it down:

• string_expression: the text you want to slice.
• start_position: where the substring starts (1-indexed in SQL).
• length: how many characters to extract.

Here’s an example:

SELECT SUBSTRING('Developer', 1, 4); -- Output: 'Deve'

You just pulled the first four characters from the word "Developer". That’s how substring SQL works at its core.

When to Use the SQL Substring Function

You’ll reach for the SQL substring command anytime you need to extract part of a string. It comes in handy across different scenarios.

Isolate Data for Formatting

For example, suppose you store dates as strings. You can extract the year like this:

SELECT SUBSTRING('2024-08-15', 1, 4); -- Output: '2024'

It’s a simple fix if your dates aren't stored in a proper datetime format.

Extract Codes from IDs

Say you have product IDs like "SKU-98765" and want just the number:

SELECT SUBSTRING('SKU-98765', 5); -- Output: '98765'

By skipping the first four characters, you get the useful part of the ID.

Modify Output in Reports

In a reporting system, you might want to display shortened versions of long strings, such as showing only the first 10 characters of a review:

SELECT SUBSTRING(review_text, 1, 10) AS short_review
FROM product_reviews;

This trims unnecessary details while still giving users an idea of the content.

Examples of SQL Substring in Practice

Example 1: Substring in SQL With Column Data

SELECT SUBSTRING(name, 1, 3) AS name_prefix
FROM employees;

This gives you the first three letters of every employee’s name. You can use this to group, sort, or classify data.

Example 2: Using Substr SQL Query With JOIN

You can also use the substring SQL function within joins. Imagine product codes include region data at the start:

SELECT *
FROM products p
JOIN regions r
ON SUBSTRING(p.product_code, 1, 2) = r.region_code;

This lets you join on partial strings when full matching isn't possible.

Example 3: SQL Replace Substring

You can use substring functions alongside REPLACE() to modify values:

SELECT REPLACE(SUBSTRING(description, 1, 5), 'old', 'new')
FROM items;

You’re pulling a part of the text and cleaning it up in one go.

Example 4: SQL Contains Substring Filter

You can use LIKE to find rows that contain a substring:

SELECT *
FROM customers
WHERE name LIKE '%son%';

That pulls all names that contain “son”. While this doesn’t use the substring function directly, it supports the concept of substring in SQL-based filtering.

Learn More About SQL Substring

The Difference Between SUBSTRING and SUBSTR

In most databases, SUBSTRING and SUBSTR do the same thing. Some databases prefer one or the other.

• MySQL supports both.
• SQL Server uses SUBSTRING.
• Oracle prefers SUBSTR.

Example using substr SQL query:

SELECT SUBSTR('Engineering', 4, 3); -- Output: 'ine'

Same idea, slightly different naming.

SQL Substring Index

MySQL includes a unique version of substring called SUBSTRING_INDEX. This splits strings based on a delimiter.

SELECT SUBSTRING_INDEX('a/b/c/d', '/', 2); -- Output: 'a/b'

You grab a portion of the string based on a delimiter and a count. A negative value starts from the end.

SELECT SUBSTRING_INDEX('a/b/c/d', '/', -1); -- Output: 'd'

This is a lifesaver when dealing with file paths or domain names.

SQL Update With Substring

You can use SQL substring inside an UPDATE statement to modify data in place:

UPDATE employees
SET first_initial = SUBSTRING(name, 1, 1)
WHERE first_initial IS NULL;

You populate a new column with the first letter of each name.

Combining SQL Substring With CASE

Here’s how to handle conditional logic using the substring command in SQL:

SELECT
  CASE
    WHEN SUBSTRING(phone, 1, 3) = '555' THEN 'Internal'
    ELSE 'External'
  END AS phone_type
FROM contacts;

This gives you powerful ways to classify, group, or label rows.

Nested Substring Function in SQL Query

Sometimes you want to chain substring operations:

SELECT SUBSTRING(SUBSTRING(email, 1, 10), 3, 5)
FROM users;

You can nest SQL substring calls to refine results even more.

Combining SQL Substring With Trim or Length

Let’s say you want to extract the last five characters of a field:

SELECT SUBSTRING(code, LENGTH(code) - 4, 5)
FROM inventory;

This starts at the fifth-last character and grabs five letters. Great for suffixes, versions, or trailing codes.

Using SQL Substring in Joins and Filters

Sometimes you’ll join or filter tables using a partial match from substring:

SELECT *
FROM files f
JOIN users u
ON SUBSTRING(f.filename, 1, 3) = u.department_code;

You’re matching filenames to user departments without storing full metadata.

SQL Substring in Stored Procedures

Within a stored procedure or trigger, you might use substring SQL to transform data before inserting or updating:

CREATE PROCEDURE insert_summary()
BEGIN
  INSERT INTO summary_table (snippet)
  SELECT SUBSTRING(content, 1, 100) FROM articles;
END;

You’re storing a summary version without relying on the application to generate it.

Edge Cases in SQL Substring

• If start_position exceeds the string length, you get an empty result.
• If length goes beyond the string end, the result includes as much as possible.
• Many databases treat 1 as the first character (not 0).

SQL Substring Function With Unicode or Multibyte Strings

With multibyte strings like emojis or non-Latin characters, some databases may treat each byte or character differently. Use caution and test the output, especially with UTF-8 strings. In PostgreSQL or modern MySQL versions, this usually works fine out of the box.

The SQL substring function is one of the most practical tools in your SQL toolkit. Whether you’re cleaning messy inputs, extracting specific characters, or applying logic based on string content, substring SQL queries give you control. You can filter, group, join, and transform data efficiently by slicing just the parts you need.