Introduction

Extracting the first ten characters of a column is a common SQL requirement for previews, reports, and lightweight search displays. In MySQL, this is straightforward with string functions, but correctness and performance depend on character encoding, null behavior, and filtering strategy. A good solution uses the right function for readability and avoids turning simple display logic into expensive query patterns.

Basic Extraction With LEFT

The most direct approach is LEFT(column, 10).

1SELECT
2  id,
3  LEFT(description, 10) AS description_prefix
4FROM products;

LEFT returns the first n characters from the string. If the value is shorter than ten, MySQL returns the full value. If the column is NULL, result is NULL.

This is usually the cleanest expression for prefix extraction.

Equivalent Using SUBSTRING

You can do the same with SUBSTRING.

1SELECT
2  id,
3  SUBSTRING(description, 1, 10) AS description_prefix
4FROM products;

Both forms are valid. Teams often prefer LEFT for this exact use case because the intent is immediately obvious.

Handling Null and Empty Values

If you need a non-null result, wrap with COALESCE.

1SELECT
2  id,
3  LEFT(COALESCE(description, ''), 10) AS description_prefix
4FROM products;

This is useful for CSV exports or APIs where null handling should be consistent.

Character Set Considerations

MySQL string functions operate on characters, not bytes, when using character data types and proper collations. With UTF-8 text, this usually gives safe visible prefixes. Still, display semantics can be tricky for combined emoji or multi-code-point grapheme clusters, where ten characters by database rules may not equal ten user-perceived symbols.

For user-facing truncation in multilingual UI, it can be better to keep database extraction simple and apply final display truncation in application code where locale-aware rendering is available.

Prefix Filtering Versus Prefix Display

Do not confuse extraction with filtering. If you are searching by prefix, this is better:

SELECT id, description
FROM products
WHERE description LIKE 'micro%';

Using LEFT(description, 5) = 'micro' can make index usage less efficient in many cases. Prefix extraction in SELECT is fine. Prefix logic in WHERE should be designed for index-friendly matching.

Sorting by Prefix

If reporting requires sort by first ten characters:

1SELECT
2  id,
3  LEFT(description, 10) AS prefix
4FROM products
5ORDER BY prefix;

This works, but sorting by expression may be expensive on large tables. If this becomes a hotspot, consider a generated column.

Generated Column for Frequent Prefix Use

For heavy workloads, compute and index prefix once.

1ALTER TABLE products
2ADD COLUMN description_prefix VARCHAR(10)
3    GENERATED ALWAYS AS (LEFT(description, 10)) STORED,
4ADD INDEX idx_description_prefix (description_prefix);

Then query directly:

SELECT id, description_prefix
FROM products
WHERE description_prefix = 'Microphone';

This can improve performance for frequent prefix-based reporting and filtering.

Trimming and Normalization Before Extraction

If source data has inconsistent leading spaces, normalize first.

1SELECT
2  id,
3  LEFT(TRIM(description), 10) AS normalized_prefix
4FROM products;

A prefix from untrimmed strings can produce confusing report output.

API and Report Usage Pattern

A common pattern is returning both full value and preview value:

1SELECT
2  id,
3  description,
4  LEFT(description, 10) AS preview
5FROM products
6LIMIT 100;

This avoids extra processing in clients while preserving full data for drill-down.

Common Pitfalls

• Using LEFT in filtering clauses where index-friendly prefix search is needed.
• Assuming ten database characters always equals ten user-visible symbols.
• Forgetting null handling and emitting unexpected null preview values.
• Repeating expensive expression sorting on very large datasets without optimization.
• Mixing display truncation and business logic rules in the same SQL expression.

Summary

• Use LEFT(column, 10) as the default way to get first ten characters.
• 'SUBSTRING(column, 1, 10) is equivalent when preferred by team style.'
• Handle null and whitespace normalization deliberately.
• Keep prefix display logic separate from index-sensitive filtering strategy.
• For frequent heavy use, consider generated prefix columns with indexes.