SUBSTRING extraction in SQL - Step-by-Step Execution

Concept Flow - SUBSTRING extraction

Start with full string

↓

Specify start position and length

↓

Extract substring from start position

↓

Return extracted substring

↓

End

The SUBSTRING function takes a full string, a start position, and a length, then extracts and returns the part of the string starting at that position with the given length.

Execution Sample

SQL

SELECT SUBSTRING('Hello World', 7, 5) AS extracted;

Extracts 5 characters from 'Hello World' starting at position 7.

Execution Table

Step	Input String	Start Position	Length	Action	Result
1	'Hello World'	7	5	Identify substring starting at 7 for length 5	'World'
2	N/A	N/A	N/A	Return extracted substring	'World'
3	N/A	N/A	N/A	End of execution	Execution stops

💡 Extraction complete; substring 'World' returned from position 7 with length 5.

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
Input String	'Hello World'	'Hello World'	N/A	N/A
Start Position	7	7	N/A	N/A
Length	5	5	N/A	N/A
Extracted Substring	N/A	'World'	'World'	'World'

Key Moments - 2 Insights

Why does the substring start at position 7 and not 6?

In SQL, string positions start at 1, so position 7 points to the 'W' in 'Hello World'. This is shown in execution_table row 1 where start position 7 extracts 'World'.

What happens if the length exceeds the remaining string length?

The substring function extracts up to the end of the string without error. For example, if length was 10 starting at 7, it would return 'World' anyway, as shown by the logic in execution_table row 1.

Visual Quiz - 3 Questions

Look at the execution table, what is the extracted substring at step 1?

A 'Hello'

B 'World'

C 'Hello World'

D 'orld'

💡 Hint

Check the 'Result' column in execution_table row 1.

At which step does the function return the extracted substring?

A Step 2

B Step 1

C Step 3

D Step 4

💡 Hint

Look for the action 'Return extracted substring' in execution_table.

If the start position was 1 and length 5, what would the extracted substring be?

A 'World'

B 'Hello World'

C 'Hello'

D 'ello '

💡 Hint

Refer to how start position affects extraction in variable_tracker and concept_flow.

Concept Snapshot

SUBSTRING(string, start, length)
Extracts part of string starting at 'start' position (1-based)
Returns substring of given 'length'
If length exceeds string end, returns up to string end
Used to get parts of text data in SQL queries

Full Transcript

The SUBSTRING function in SQL extracts a part of a string starting from a specified position and for a specified length. Positions start at 1, meaning the first character is position 1. For example, SUBSTRING('Hello World', 7, 5) extracts 'World' because it starts at the 7th character and takes 5 characters. If the length is longer than the remaining string, it extracts until the end without error. The function returns the extracted substring as the result.

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