SUBSTRING() Function in SQL
Introduction
SUBSTRING is a string function used to extract a selected portion of text. A query supplies a source value, a starting position, and usually the number of characters to return. The function is useful for fixed-format codes, labels, initials, previews, imports, reports, and controlled parsing.
SELECT SUBSTRING('Software Testing', 1, 8) AS extracted_text;
-- Result: Software
The example starts at the first character and returns eight characters. Although this looks simple, reliable use requires attention to database syntax, one-based positions, delimiters, NULL values, Unicode, boundary behavior, and performance. Oracle commonly uses SUBSTR, PostgreSQL supports a standard FROM/FOR form, and zero or negative positions are not portable.
SUBSTRING should extract text, not compensate indefinitely for poor data modeling. Dates, money, identifiers, and structured attributes should normally use appropriate types and columns. Extraction works best when the source format is documented and stable or when positions are calculated from dependable delimiters.
Basic SUBSTRING Syntax
A widely supported form uses three arguments. The first is the source expression, the second is the starting position, and the third is the number of units to return.
SUBSTRING(string_expression, start_position, length)
The third argument is a length, not an ending position. In SUBSTRING('Database', 5, 4), 5 means begin at the fifth character and 4 means return four characters.
SELECT SUBSTRING('Database', 1, 4) AS extracted_text;
-- Result: Data
SQL Positions Commonly Start at One
Many programming languages index strings from zero, but common SQL substring usage starts with position 1. The first character of SQL is therefore extracted with start position 1.
SELECT SUBSTRING('SQL', 1, 1) AS first_character;
-- Result: S
Behavior at position zero differs by DBMS and should not be used as a portability shortcut. Writing one for the first character makes the intention clear to reviewers.
Extracting the First Characters
A prefix uses start position 1 and the required prefix length. In the word Database, the first four characters form Data.
SELECT SUBSTRING('Database', 1, 4) AS prefix;
-- Result: Data
This pattern is reliable when the required unit is known. If the prefix ends at a delimiter rather than a fixed position, calculate the delimiter position instead of assuming every value has the same layout.
Extracting from the Middle
Starting at position 5 in Database and taking four characters returns base. Counting the source carefully prevents the common off-by-one error.
SELECT SUBSTRING('Database', 5, 4) AS extracted_text;
-- Result: base
The engine does not understand syllables or words. It returns units from the position supplied, even if that position splits a word, code, or user-perceived symbol.
Extracting a Single Character
A length of 1 returns one character according to the DBMS string semantics. The third character in Database is t.
SELECT SUBSTRING('Database', 3, 1) AS third_character;
-- Result: t
Single-character extraction is useful in classification and validation, although a direct predicate or schema rule may communicate the business meaning more clearly.
Using SUBSTRING with a Column
When the source is a column, extraction occurs for every selected row. Product codes such as ELEC-1001, FOOD-2001, and BOOK-3001 can expose their four-character category prefix.
SELECT product_code,
SUBSTRING(product_code, 1, 4) AS category_code
FROM products;
Use a meaningful alias so downstream tools receive a clear column name. If category is an important independent attribute used for joins and constraints, storing it in a proper related column is usually better than deriving it repeatedly.
Extracting a Known Prefix
Employee codes such as USA-1001 and IND-1003 contain a three-character country prefix. Fixed-position extraction is appropriate only when the code contract guarantees this format.
SELECT employee_code,
SUBSTRING(employee_code, 1, 3) AS country_code
FROM employees;
Rows that are shorter, malformed, or unexpectedly padded should be identified separately. Otherwise, extraction can return plausible-looking but incorrect values.
Extracting a Suffix from a Known Position
For an eight-character code such as EMP-1001, the numeric portion begins at position 5 and contains four characters.
SELECT employee_code,
SUBSTRING(employee_code, 5, 4) AS employee_number
FROM employees;
This fixed length fails when suffixes grow to five or six digits. If the remainder should be returned regardless of size, use a supported to-the-end syntax or calculate the required length.
Extracting to the End of a String
Some databases let the length be omitted. PostgreSQL supports the standard-style form SUBSTRING(value FROM start), while other systems provide different syntax or require a calculated length.
SELECT SUBSTRING(employee_code FROM 5) AS employee_number
FROM employees;
Where a length is required, use the platform's correct character-length function. Do not use a byte length to calculate a character substring without confirming compatible semantics.
Calculating Length Dynamically
A dynamic expression can request everything remaining after a fixed prefix. For EMP-1001, a source length of eight minus the four-character prefix leaves four characters.
SELECT employee_code,
SUBSTRING(employee_code, 5, LENGTH(employee_code) - 4) AS employee_number
FROM employees;
The exact LENGTH semantics differ by DBMS. MySQL LENGTH returns bytes, so CHAR_LENGTH is generally safer when SUBSTRING positions are character based and multilingual input is possible.
Standard FROM and FOR Syntax
SQL also has a readable syntax that names the starting position and length with FROM and FOR. PostgreSQL supports this form, and MySQL supports related forms.
SELECT SUBSTRING('Database' FROM 1 FOR 4) AS extracted_text;
-- Result: Data
The comma form and FROM/FOR form communicate the same core operation, but availability differs. Follow the conventions of the target system and project rather than assuming one form is universal.
SUBSTRING Versus SUBSTR
SUBSTR is a related function name used by Oracle and supported by several other databases. It usually accepts a source, start, and optional length.
SELECT SUBSTR('Database', 1, 4)
FROM dual;
-- Oracle result: Data
Similar names do not guarantee identical edge behavior. Negative positions, omitted lengths, large objects, binary values, and Unicode units should be checked in the target documentation.
MySQL SUBSTRING
MySQL supports SUBSTRING and SUBSTR as synonyms, with comma and FROM/FOR styles. Positions are character based, while MySQL LENGTH returns bytes and CHAR_LENGTH returns characters.
SELECT SUBSTRING('Database', 1, 4),
SUBSTRING('Database' FROM 5 FOR 4);
MySQL also supports negative positions that count from the end, but relying on them reduces portability. Use explicit logic when SQL must run consistently across products.
PostgreSQL SUBSTRING
PostgreSQL supports standard FROM/FOR syntax and pattern-oriented forms. Straight positional extraction is clearest for fixed character positions.
SELECT SUBSTRING('Database' FROM 1 FOR 4) AS prefix;
Pattern extraction is powerful but should not be introduced when a simple position or delimiter function is easier to understand. Keep parsing rules reviewable and test unusual input.
SQL Server SUBSTRING
SQL Server commonly uses SUBSTRING(expression, start, length). A length is traditionally required, and LEN excludes trailing spaces while DATALENGTH reports bytes.
SELECT SUBSTRING('Database', 1, 4) AS prefix;
When dynamic extraction depends on the source length, account for LEN's trailing-space behavior. The correct expression depends on whether trailing spaces are meaningful.
Oracle SUBSTR
Oracle commonly uses SUBSTR rather than SUBSTRING. The start position may be positive or negative, and omitting the length returns the remainder.
SELECT SUBSTR('Database', 5, 4) AS extracted_text
FROM dual;
-- Result: base
Oracle treats an empty character string as NULL. Migration tests should therefore include empty input and out-of-range results, not only ordinary text.
SQLite SUBSTR and SUBSTRING
SQLite supports substr and newer aliases such as substring. It allows negative starts and has its own rules for omitted or negative lengths.
SQLite's flexible typing can hide assumptions that fail on a stricter server database. Test the production DBMS instead of treating local SQLite behavior as a universal contract.
Cross-Database Comparison
| Database | Common form | Important note |
|---|---|---|
| MySQL | SUBSTRING(value, start, length) | Also supports SUBSTR and FROM/FOR forms |
| PostgreSQL | SUBSTRING(value FROM start FOR length) | Also provides pattern forms |
| SQL Server | SUBSTRING(value, start, length) | Check version-specific omitted-length support |
| Oracle | SUBSTR(value, start, length) | Negative starts and omitted length are supported |
| SQLite | SUBSTR(value, start, length) | Edge behavior is SQLite-specific |
NULL Input
SUBSTRING of NULL normally returns NULL because no source text is available. The function does not convert missing data into an empty string.
SELECT SUBSTRING(NULL, 1, 5) AS extracted_text;
-- Typical result: NULL
A WHERE comparison against this result is unknown rather than true or false. Use IS NULL when missing source data needs its own category.
Handling NULL with COALESCE
COALESCE can provide a fallback before extraction when the business rule deliberately treats missing text as empty or as a label.
SELECT SUBSTRING(COALESCE(description, ''), 1, 20) AS preview
FROM products;
This changes the meaning of the data. Use it for presentation when appropriate, but do not silently erase the difference between missing and empty values in quality reports.
Cleaning Input with TRIM
Leading spaces shift fixed positions. When surrounding spaces are invalid input noise, trim first and then extract.
SELECT product_code,
SUBSTRING(TRIM(product_code), 1, 4) AS category_code
FROM products;
TRIM does not necessarily remove tabs, non-breaking spaces, or every Unicode whitespace character. Define the cleanup rule and preserve raw source values when auditability matters.
Combining SUBSTRING with UPPER and LOWER
Extraction can occur before case conversion. This is useful for normalized display labels or comparisons where the collation does not already provide the desired case behavior.
SELECT UPPER(SUBSTRING(employee_name, 1, 3)) AS name_prefix,
LOWER(SUBSTRING(product_code, 1, 4)) AS category_label
FROM employees;
Unicode case mappings can change length or representation. If position rules apply after normalization or case conversion, transform first and extract from the transformed result.
Generating Initials
The first character of each known name component can be concatenated to produce initials.
SELECT first_name,
last_name,
CONCAT(
UPPER(SUBSTRING(first_name, 1, 1)),
UPPER(SUBSTRING(last_name, 1, 1))
) AS initials
FROM employees;
Real names do not always consist of exactly two simple fields. NULL components, multiple family names, prefixes, combining marks, and cultural naming conventions require a broader product decision than this demonstration query.
Creating Text Previews
SUBSTRING can shorten long descriptions for reports or search-result previews without changing the stored value.
SELECT product_name,
SUBSTRING(description, 1, 50) AS description_preview
FROM products;
A database preview may split a word or user-perceived Unicode symbol. User interfaces often have better context for word-aware and locale-aware truncation, but SQL previews remain useful for exports and administrative reports.
Adding an Ellipsis Only When Needed
Appending three dots unconditionally makes short values appear truncated. CASE can add an ellipsis only when the source exceeds the preview limit.
SELECT CASE
WHEN CHAR_LENGTH(description) > 50
THEN CONCAT(SUBSTRING(description, 1, 50), '...')
ELSE description
END AS description_preview
FROM products;
Choose the DBMS-appropriate character length function. An ellipsis character and three periods also have different byte and display widths, which can matter in strict interfaces.
Finding a Delimiter Position
Variable-format strings should be parsed by locating a stable delimiter. Function names differ: MySQL offers LOCATE and INSTR, PostgreSQL offers POSITION, SQL Server offers CHARINDEX, and Oracle offers INSTR.
-- MySQL example
SELECT email,
SUBSTRING(email, 1, LOCATE('@', email) - 1) AS email_user
FROM customers;
Validate that the delimiter exists before subtracting or extracting. Missing, repeated, or misplaced delimiters can produce errors, empty output, or misleading partial values.
Extracting Text After a Delimiter
To extract an email domain, begin after the at sign and continue to the end. The following PostgreSQL example uses POSITION and a to-the-end form.
SELECT email,
SUBSTRING(email FROM POSITION('@' IN email) + 1) AS email_domain
FROM customers
WHERE POSITION('@' IN email) > 0;
This demonstrates extraction, not complete email validation. Quoted local parts, internationalized addresses, and malformed input require more deliberate rules.
Repeated Delimiters and the Last Occurrence
File extensions and hierarchical paths often require the last delimiter rather than the first. A filename may contain multiple dots, such as archive.2026.11.zip.
Databases use different combinations of reverse, position, split, or regular-expression functions to locate the final dot. Prefer a purpose-built split function when available, and define behavior for names without an extension, hidden files, and a trailing dot.
Extracting Fixed Text Dates
A legacy ISO-style string such as 2026-09-26 can be divided into year, month, and day with fixed positions.
SELECT SUBSTRING(date_text, 1, 4) AS year_text,
SUBSTRING(date_text, 6, 2) AS month_text,
SUBSTRING(date_text, 9, 2) AS day_text
FROM legacy_records;
This is safe only after format validation. A real DATE column should use date extraction functions because they understand calendar semantics, support date arithmetic, and prevent invalid strings.
Extracting Fixed Phone Components
A normalized ten-digit domestic phone string can expose its first three digits with a fixed substring.
SELECT customer_id,
SUBSTRING(normalized_phone, 1, 3) AS area_code
FROM customers;
Raw phone numbers may include country codes, parentheses, spaces, extensions, and variable national formats. Normalize and validate them before relying on fixed positions, and do not assume that every country uses the same component lengths.
Using SUBSTRING in WHERE
A predicate can compare an extracted prefix, but the database may need to evaluate the function for many rows.
SELECT product_id, product_code
FROM products
WHERE SUBSTRING(product_code, 1, 4) = 'ELEC';
This is logically valid. When the actual requirement is simply starts with ELEC, a direct prefix predicate is usually clearer and may use an ordinary index more effectively.
Prefer Prefix Predicates When Appropriate
LIKE expresses a prefix requirement directly.
SELECT product_id, product_code
FROM products
WHERE product_code LIKE 'ELEC%';
Index use still depends on collation, data type, pattern, database, and execution plan. A leading wildcard such as %ELEC has different performance characteristics and does not represent the same business rule.
SUBSTRING and Index Performance
Applying SUBSTRING to an indexed column can make a predicate non-sargable with respect to a normal index. The engine may scan rows, compute the expression, and compare each result.
For frequent selective searches, consider a generated column, computed column, expression index, function-based index, or separate modeled attribute supported by the DBMS. Use representative execution plans and measurements rather than relying on a generic rule.
SUBSTRING in JOIN Conditions
Joining tables by parsing a code at query time is often a warning sign. It couples the relationship to formatting and can make both correctness and performance fragile.
-- Avoid as a permanent relationship design
SELECT p.product_id, c.category_name
FROM products p
JOIN categories c
ON SUBSTRING(p.product_code, 1, 4) = c.category_code;
A real foreign key column is generally safer. It can enforce referential integrity, remain stable when display formats change, and support direct indexing.
Sorting and Grouping by Extracted Text
SUBSTRING can define a reporting category in ORDER BY or GROUP BY. Repeating the expression is common where aliases are not accepted in grouping.
SELECT SUBSTRING(product_code, 1, 4) AS category_code,
COUNT(*) AS product_count
FROM products
GROUP BY SUBSTRING(product_code, 1, 4)
ORDER BY category_code;
This is appropriate for exploration or stable encoded categories. A modeled category column is better when the group has independent meaning, metadata, or integrity rules.
Distinct Extracted Values
DISTINCT can list the different prefixes or fragments present in a data set.
SELECT DISTINCT SUBSTRING(product_code, 1, 4) AS category_code
FROM products
ORDER BY category_code;
Review NULL, empty, short, and malformed codes separately. A distinct fragment alone can conceal different invalid source patterns that happen to produce the same output.
Negative Starting Positions
MySQL, Oracle, SQLite, and some related functions allow negative starts that count backward from the end. For example, a supported SUBSTR('Database', -4) can return base.
Negative starts are convenient but not portable. SQL Server and standard positional expectations differ. For cross-database code, calculate from the character length or use a platform abstraction with tests.
Start Position Zero
Position zero has inconsistent behavior. Some systems treat it similarly to one in particular cases, while others adjust the requested length or reject the expectation.
Avoid position zero in portable SQL. Use position 1 for the first character and test all calculated positions before passing them to the function.
Start Beyond the End
If the start position lies beyond the available text, many databases return an empty string. Oracle's empty-string semantics can turn that result into NULL.
SELECT SUBSTRING('SQL', 10, 5) AS extracted_text;
Do not use an empty result as proof that the source itself was empty. It may indicate an out-of-range position, a missing delimiter, or an incorrectly calculated offset.
Length Greater Than Remaining Text
When the requested length exceeds the remaining source, common implementations return the available remainder without padding it.
SELECT SUBSTRING('SQL', 1, 100) AS extracted_text;
-- Common result: SQL
This behavior makes preview queries convenient, but strict validation should still compare the source length when a complete fixed-width field is required.
Zero and Negative Lengths
A zero length commonly returns an empty string, subject to Oracle's empty-string treatment. A negative length may raise an error or trigger DBMS-specific behavior.
Validate calculated lengths before extraction. A missing delimiter can make position - 1 negative, turning ordinary bad input into a runtime failure.
Unicode and Character Boundaries
String positions generally follow the DBMS's character semantics, not raw byte offsets. This is essential for Tamil, Chinese, Japanese, accented text, and emoji because one visible symbol can occupy several bytes.
Even character-oriented extraction may split a user-perceived grapheme. A displayed letter can consist of a base code point plus combining marks, and an emoji can be a joined sequence. UI-facing truncation may need a grapheme-aware application library.
Binary Data Requires Binary Semantics
Some databases permit substring operations on binary values, but text and binary overloads can use different units and return types. Do not decode or slice encrypted, compressed, or encoded data as if it were ordinary text.
Use documented binary functions and preserve protocol boundaries. Splitting bytes at an arbitrary offset can corrupt a multibyte text encoding or structured binary format.
Masking Sensitive Display Values
SUBSTRING is sometimes used to show only a controlled portion of an account number, phone number, or identifier. For example, a report might preserve the final four characters while replacing the earlier portion with a fixed mask.
SELECT CONCAT('****', SUBSTRING(account_reference, 9, 4)) AS masked_reference
FROM customer_accounts;
This is presentation masking, not security. The original value remains available to the query and may still appear in logs, exports, backups, or unauthorized queries. Sensitive fields need access controls, encryption where appropriate, auditing, and purpose-specific views. Also validate the source length before using fixed positions; short values can reveal unexpected content or produce inconsistent output.
Collation and Comparison After Extraction
SUBSTRING chooses a portion of text, while collation influences how the extracted result is compared and sorted. Case sensitivity, accent sensitivity, and language-specific ordering can therefore change the behavior of filters and reports even when extraction returns the expected characters.
A prefix comparison that works in a case-insensitive collation may produce different results after migration to a case-sensitive environment. Document both the extraction rule and comparison semantics. Do not add UPPER or LOWER automatically without considering index use, Unicode case mapping, and whether the selected collation already implements the required behavior.
Generated Columns for Repeated Extraction
When an extracted component is queried frequently and the encoded source format cannot yet be redesigned, a generated or computed column may expose that component explicitly. Depending on the DBMS, it can also support an index and constraint.
-- Conceptual pattern; syntax varies by DBMS
ALTER TABLE products
ADD category_code AS (SUBSTRING(product_code, 1, 4));
This approach makes a repeated rule visible but also formalizes dependence on the source format. If the code format changes, the generated expression, indexes, constraints, reports, and migration logic must change together. A normal modeled category column with a foreign key remains preferable when category is a genuine business entity.
Safe Logging and Diagnostic Snippets
Operational queries may extract short snippets to help diagnose malformed messages or imported records. Limit the result deliberately and avoid cutting confidential values into logs merely because a full field is too long.
Decide which fields may be logged, redact secrets before extraction, and record enough context to find the source row without exposing its entire content. Remember that a character limit does not guarantee a byte limit in UTF-8, and a byte-oriented log transport can still reject or truncate multilingual snippets. Test the complete logging path rather than only the SQL expression.
Avoid Parsing Structured Data Repeatedly
A value such as 101|John|Chicago|Active can be split, but storing customer ID, name, city, and status in separate columns provides better types, constraints, indexing, and clarity.
SUBSTRING is reasonable at an ingestion boundary or for a controlled legacy migration. It should not become the permanent access method for attributes the database should model directly.
Avoid Parsing Numbers as Text
Currency text such as $1,250.50 is presentation, not a dependable numeric representation. Substring extraction is fragile across currency symbols, separators, locales, and negative formats.
Store amounts as DECIMAL or NUMERIC, perform calculations numerically, and format them for users at the presentation boundary. The same principle applies to identifiers that are numeric only by appearance.
Common Off-by-One Mistakes
To extract base from Database, start at 5 rather than 4. The fourth character is a, so starting there returns abase when five units are requested.
Write representative source text with numbered positions during development and test the first, middle, and last expected values. Boundary tests catch errors that ordinary examples miss.
Do Not Confuse Length with End Position
In the comma form, the third parameter says how much to take, not where to stop. SUBSTRING(value, 5, 4) starts at 5 and returns four units.
If a requirement gives start and end positions, calculate end - start + 1 for inclusive positions. Validate that the end is not before the start.
Testing Dynamic Extraction
A robust test set includes a normal value, minimum and maximum lengths, a missing delimiter, repeated delimiters, a delimiter at the beginning or end, empty text, NULL, leading and trailing spaces, Unicode, and a start beyond the source.
Run tests on every supported DBMS. Verify both the returned value and the data type, especially when moving between character and large-object types.
Migration and Portability Review
During migration, inventory every SUBSTRING, SUBSTR, position, and length function. Record whether starts may be zero or negative, whether lengths are omitted, and whether byte or character units are involved.
Translate behavior rather than names. A syntactically valid replacement can still change NULL, empty-string, Unicode, trailing-space, and out-of-range results.
Real-World Data Profiling
Extraction can help profile coded data before redesign. The following query counts first-three-character prefixes and separates short values for investigation.
SELECT CASE
WHEN customer_code IS NULL THEN 'MISSING'
WHEN CHAR_LENGTH(customer_code) < 3 THEN 'TOO_SHORT'
ELSE SUBSTRING(customer_code, 1, 3)
END AS code_group,
COUNT(*) AS row_count
FROM customers
GROUP BY CASE
WHEN customer_code IS NULL THEN 'MISSING'
WHEN CHAR_LENGTH(customer_code) < 3 THEN 'TOO_SHORT'
ELSE SUBSTRING(customer_code, 1, 3)
END;
This profile supports investigation; it does not automatically prove that every three-character prefix is valid.
Best-Practice Checklist
- Confirm the target DBMS syntax and position rules.
- Remember that common SQL positions begin at one.
- Treat the third argument as a length, not an end position.
- Use fixed positions only for documented, validated formats.
- Find delimiters dynamically when component sizes vary.
- Handle NULL, empty, missing-delimiter, and out-of-range cases explicitly.
- Use character-compatible length functions for multilingual text.
- Prefer typed dates, numbers, and separate structured columns.
- Prefer direct prefix predicates where they express the requirement.
- Inspect plans for function-based filters, joins, grouping, and sorting.
Interview Questions and Answers
What does SUBSTRING do? It extracts part of a string from a starting position, usually for a specified length.
What are the common parameters? The source string, starting position, and number of units to return.
Does SQL substring indexing start at zero? Common portable usage starts the first character at position 1. Zero and negative positions are DBMS-specific.
What happens when the source is NULL? The result is normally NULL unless a fallback is supplied.
What is SUBSTR versus SUBSTRING? They provide similar extraction. Oracle commonly uses SUBSTR; other databases support one or both names.
Can SUBSTRING use a column? Yes. It evaluates the source expression for each selected row.
How do you extract text before a delimiter? Find the delimiter with the DBMS position function, then use that position to calculate the substring length.
Can SUBSTRING affect index use? Yes. Applying it to an indexed column in a predicate can make a normal index less directly useful.
Why can LIKE be preferable for prefixes? LIKE 'ABC%' expresses starts with ABC directly and may be more index-friendly, depending on the DBMS and collation.
Should SUBSTRING parse dates and money? Not when properly typed DATE and numeric columns are available. Use type-specific functions instead.
Key Takeaway
SUBSTRING extracts a controlled portion of text by combining a source, a starting position, and usually a length. It is reliable when positions come from a stable format or validated delimiter and when database-specific boundary behavior is understood.
RELIABLE EXTRACTION = VALIDATED SOURCE FORMAT
+ CORRECT ONE-BASED POSITION
+ EXPLICIT LENGTH OR END RULE
+ NULL AND DELIMITER HANDLING
+ UNICODE-AWARE SEMANTICS
+ VERIFIED QUERY PERFORMANCE
Use SUBSTRING for genuine text extraction. Keep structured facts in typed columns, prefer direct predicates for searching, test every boundary, and translate semantics carefully when SQL moves between database products.