Substring and overlay functions in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to extract the first 5 characters from the string 'HelloWorld'.

PostgreSQL

SELECT SUBSTRING('HelloWorld' FROM 1 FOR [1]);

Drag options to blanks, or click blank then click option'

A1

B10

C0

D5

Attempts:

3 left

💡 Hint

Common Mistakes

Using 10 extracts the whole string, not just the first 5 characters.

Using 0 or 1 will not extract the correct substring length.

The SUBSTRING function extracts a part of the string starting at position 1 for 5 characters.

2fill in blank

medium

Complete the code to replace 3 characters starting at position 2 in 'abcdef' with 'XYZ'.

PostgreSQL

SELECT OVERLAY('abcdef' PLACING [1] FROM 2 FOR 3);

Drag options to blanks, or click blank then click option'

A'XYZ'

B'123'

C'abc'

D'def'

Attempts:

3 left

💡 Hint

Common Mistakes

Using '123' or other strings changes the output incorrectly.

Using 'abc' or 'def' does not replace the correct part.

The OVERLAY function replaces 3 characters starting at position 2 with 'XYZ'.

3fill in blank

hard

Fix the error in the code to extract substring starting at position 3 with length 4 from 'PostgreSQL'.

PostgreSQL

SELECT SUBSTRING('PostgreSQL' [1] 3 FOR 4);

Drag options to blanks, or click blank then click option'

AAT

BSTART

CFROM

DPOSITION

Attempts:

3 left

💡 Hint

Common Mistakes

Using AT or POSITION causes syntax errors.

Using START is not valid in PostgreSQL SUBSTRING.

The correct syntax uses FROM to specify the start position in SUBSTRING.

4fill in blank

hard

Fill both blanks to extract 4 characters starting at position 2 from 'Database' and replace them with '1234'.

PostgreSQL

SELECT OVERLAY('Database' PLACING [1] FROM [2] FOR 4);

Drag options to blanks, or click blank then click option'

A'1234'

B'Data'

C2

D4

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong start position changes the replaced part.

Using incorrect PLACING string changes the output.

The OVERLAY replaces 4 characters starting at position 2 with '1234'.

5fill in blank

hard

Fill all three blanks to extract substring starting at position 4 for 3 characters from 'Programming' and replace it with 'XYZ'.

PostgreSQL

SELECT OVERLAY(SUBSTRING('Programming' FROM [1] FOR [2]) PLACING [3] FROM 1 FOR 3);

Drag options to blanks, or click blank then click option'

A4

B3

C'XYZ'

D'Pro'

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong start position or length in SUBSTRING.

Using incorrect string in PLACING changes the result.

First, SUBSTRING extracts 3 characters starting at position 4, then OVERLAY replaces them with 'XYZ'.