SQL SUBSTRING: Extract a Fixed-Position Code With Clear Indexing

The practice task

Extract the three-character number segment and final check letter from fictional fixed-width references. The format is two prefix characters, three number characters and one final letter.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE refs(code TEXT);
INSERT INTO refs VALUES ('AB123X'),('CD987Y');

SELECT code, SUBSTR(code,3,3) AS number_part,
 SUBSTR(code,-1,1) AS check_letter
FROM refs ORDER BY code;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

Position three is the start of 123 and 987 because the first two characters are the prefix. The length argument requests three characters. In this SQLite example, the negative starting position selects from the end, returning the check letter.

The extracted number segment remains text. That is useful when codes can contain leading zeros. Converting the segment to an integer would turn a code such as 007 into 7, which may no longer match the external reference format.

Fixed-position extraction depends on a real format rule. If prefixes can vary in length or a delimiter separates fields, use a parser that matches that format instead. A substring expression can produce plausible but incorrect fragments from malformed input.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Write down the input format before selecting positions.
• Retain code segments as text when leading zeros matter.
• Test shorter or malformed references before applying the pattern widely.