SUBSTRING extraction in SQL

Introduction

We use SUBSTRING extraction to get a smaller part of a text from a bigger text. It helps us focus on just the piece we need.

You want to get the area code from a phone number stored as text.

You need to extract the first three letters of a product code.

You want to get the year from a date stored as text.

You want to shorten long text fields for display.

You want to check a part of a string for a pattern.

Syntax

SQL

SUBSTRING(string_expression FROM start_position FOR length)

start_position is where to begin extracting, counting from 1.

length is how many characters to take from the start_position.

Examples

Extracts 'Hello' starting at position 1 for 5 characters.

SQL

SELECT SUBSTRING('Hello World' FROM 1 FOR 5);

Extracts 'cd' starting at position 3 for 2 characters.

SQL

SELECT SUBSTRING('abcdef' FROM 3 FOR 2);

Extracts from position 2 to the end because length is longer than remaining text, returns 'hort'.

SQL

SELECT SUBSTRING('Short' FROM 2 FOR 10);

Start position is beyond string length, returns empty string.

SQL

SELECT SUBSTRING('Example' FROM 8 FOR 3);

Sample Program

This creates a table with product codes. Then it extracts the first 3 characters from each ProductCode.

SQL

CREATE TABLE Products (ProductID INT, ProductCode VARCHAR(20));
INSERT INTO Products VALUES (1, 'ABC123XYZ'), (2, 'DEF456'), (3, 'GHI7890');

SELECT ProductID, ProductCode, SUBSTRING(ProductCode FROM 1 FOR 3) AS CodePrefix FROM Products;

Important Notes

Time complexity is O(n) where n is the length of the substring extracted.

Space complexity is O(m) where m is the length of the substring returned.

If start_position is less than 1, some SQL systems treat it as 1.

If length is omitted in some SQL dialects, substring returns from start_position to end.

Use SUBSTRING when you need a fixed part of a string; use other functions like LEFT or RIGHT for simpler cases.

Summary

SUBSTRING extracts a part of a string starting at a position for a length.

Positions start counting at 1, not 0.

If you ask for more characters than available, it returns what it can.

Practice

1. What does the SQL function SUBSTRING('Hello World', 7, 5) return?

easy

A. 'Hello W'

B. 'Hello'

C. 'World!'

D. 'World'

Solution

1. Step 1: Understand SUBSTRING parameters

   The function takes the string 'Hello World', starts at position 7, and extracts 5 characters.

2. Step 2: Extract substring from position 7

   Counting characters: H(1) e(2) l(3) l(4) o(5) space(6) W(7) o(8) r(9) l(10) d(11). Starting at 7, 5 characters are 'World'.

3. Final Answer:

   'World' -> Option D

4. Quick Check:

   SUBSTRING('Hello World',7,5) = 'World' [OK]

Hint: Count characters starting at 1, then extract length [OK]

Common Mistakes:

• Starting count at 0 instead of 1
• Extracting fewer or more characters than length
• Including spaces incorrectly

2. Which of the following is the correct syntax to extract 3 characters starting from position 2 in SQL?

easy

A. SUBSTRING('Example', 3)

B. SUBSTRING('Example', 3, 2)

C. SUBSTRING('Example', 2, 3)

D. SUBSTRING('Example', 2)

Solution

1. Step 1: Recall SUBSTRING syntax

   SUBSTRING(string, start_position, length) extracts length characters from start_position.

2. Step 2: Match parameters to requirement

   We want 3 characters starting at position 2, so SUBSTRING('Example', 2, 3) is correct.

3. Final Answer:

   SUBSTRING('Example', 2, 3) -> Option C

4. Quick Check:

   Syntax = SUBSTRING(string, start, length) [OK]

Hint: Syntax is SUBSTRING(string, start, length) [OK]

Common Mistakes:

• Swapping start and length parameters
• Omitting length parameter
• Using zero-based start position

3. Given the table Users with a column username containing 'john_doe123', what is the result of SELECT SUBSTRING(username, 6, 4) FROM Users;?

medium

A. 'doe1'

B. 'doe_'

C. '_doe'

D. 'doe12'

Solution

1. Step 1: Identify substring start and length

   Start at position 6 and extract 4 characters from 'john_doe123'.

2. Step 2: Count characters and extract

   Characters: j(1) o(2) h(3) n(4) _(5) d(6) o(7) e(8) 1(9) 2(10) 3(11). Starting at 6: d(6), o(7), e(8), 1(9) -> 'doe1'.

3. Final Answer:

   'doe1' -> Option A

4. Quick Check:

   SUBSTRING('john_doe123',6,4) = 'doe1' [OK]

Hint: Count characters carefully starting at 1 [OK]

Common Mistakes:

• Starting count at 0
• Extracting wrong number of characters
• Confusing underscore with letter

4. Identify the error in the SQL query: SELECT SUBSTRING('Database', 0, 4);

medium

A. Start position cannot be zero

B. Length cannot be less than 5

C. String must be in double quotes

D. SUBSTRING requires only two parameters

Solution

1. Step 1: Check SUBSTRING start position rules

   In SQL, string positions start at 1, not 0.

2. Step 2: Analyze given query

   Start position is 0, which is invalid and causes an error.

3. Final Answer:

   Start position cannot be zero -> Option A

4. Quick Check:

   Positions start at 1, zero is invalid [OK]

Hint: Start counting at 1, never zero [OK]

Common Mistakes:

• Using zero as start position
• Confusing string quotes
• Wrong number of parameters

5. You have a column product_code with values like 'AB12345XYZ'. Write a SQL query to extract the numeric part (5 digits) starting from position 3. Which query correctly extracts '12345'?

hard

A. SELECT SUBSTRING(product_code, 4, 5) FROM products;

B. SELECT SUBSTRING(product_code, 3, 5) FROM products;

C. SELECT SUBSTRING(product_code, 3, 4) FROM products;

D. SELECT SUBSTRING(product_code, 2, 5) FROM products;

Solution

1. Step 1: Understand the string structure

   'AB12345XYZ' has 'A'(1), 'B'(2), '1'(3), '2'(4), '3'(5), '4'(6), '5'(7), 'X'(8), 'Y'(9), 'Z'(10).

2. Step 2: Extract 5 digits starting at position 3

   Starting at 3, extract 5 characters: '1'(3), '2'(4), '3'(5), '4'(6), '5'(7) -> '12345'.

3. Final Answer:

   SELECT SUBSTRING(product_code, 3, 5) FROM products; -> Option B

4. Quick Check:

   Start=3, length=5 extracts '12345' [OK]

Hint: Count start position carefully, length matches digits needed [OK]

Common Mistakes:

• Starting extraction at wrong position
• Extracting fewer or more characters
• Confusing character positions