SUBSTRING extraction in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SQL function SUBSTRING() do?

It extracts a part of a string starting from a specified position for a specified length.

beginner

Write the syntax of the SUBSTRING() function in SQL.

SUBSTRING(string, start_position, length) where string is the text, start_position is where to start, and length is how many characters to take.

beginner

If you run SUBSTRING('Hello World', 7, 5), what is the output?

The output is 'World' because it starts at position 7 and takes 5 characters.

intermediate

What happens if the length in SUBSTRING() is longer than the remaining string?

SQL returns all characters from the start position to the end of the string without error.

intermediate

Can SUBSTRING() start extracting from a position beyond the string length?

No, if the start position is beyond the string length, the result is an empty string.

What does SUBSTRING('Database', 3, 4) return?

A 'abat'

B taba'

C 'taba

D 'taba'

Starting at position 3, the substring is 'taba' (characters 3 to 6).

If you want to extract the first 5 characters of a string, which is correct?

A SUBSTRING(string, 1, 5)

B SUBSTRING(string, 5, 1)

C SUBSTRING(string, 0, 5)

D SUBSTRING(string, 1)

Start at position 1 and take 5 characters.

What will SUBSTRING('Hello', 10, 3) return?

A 'lo'

B 'Hel'

C Empty string

D Error

Start position 10 is beyond string length, so result is empty.

Which part of the string does SUBSTRING('Example', 4, 10) extract?

A 'Exam'

B 'mple'

C 'ple'

D 'Example'

Starts at position 4 and extracts to the end since length exceeds remaining characters.

What is the starting index for SUBSTRING() in SQL?

A 1

B 0

C -1

D Depends on the database

SQL strings start at position 1 for substring extraction.

Explain how the SUBSTRING function works in SQL with an example.

Think about how you cut a piece from a longer string.

What happens if the length parameter in SUBSTRING is longer than the remaining string?

Imagine cutting more than what is left on a rope.