Substring and overlay functions in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the substring function do in PostgreSQL?

The substring function extracts a part of a string based on position and length. For example, substring('hello' from 2 for 3) returns 'ell'.

beginner

How do you use the overlay function in PostgreSQL?

The overlay function replaces a part of a string with another string. Syntax: overlay(original_string placing new_string from start_position for length). It returns the modified string.

beginner

Example: What is the result of substring('abcdef' from 3 for 2)?

It returns 'cd' because it starts at position 3 and takes 2 characters.

beginner

Example: What does overlay('abcdef' placing 'XY' from 2 for 3) return?

It returns 'aXYef'. It replaces 3 characters starting at position 2 with 'XY'.

intermediate

Can substring use regular expressions in PostgreSQL?

Yes, substring can extract parts of a string using a regular expression pattern. For example, substring('abc123def' from '\d+') returns '123'.

What does substring('hello world' from 7 for 5) return?

A 'world'

B 'hello'

C 'world!'

D 'hello world'

It extracts 5 characters starting at position 7, which is 'world'.

Which function replaces part of a string with another string in PostgreSQL?

A overlay

B substring

C replace

D concat

overlay replaces a substring with another string at a specified position.

What is the result of overlay('abcdef' placing 'XY' from 3 for 2)?

A 'XYcdef'

B 'aXYdef'

C 'abcXYf'

D 'abXYef'

It replaces 2 characters starting at position 3 with 'XY', resulting in 'abXYef'.

Can substring extract text using a regular expression?

A Only in MySQL

B No

C Yes

D Only with overlay

PostgreSQL's substring supports regex patterns to extract matching parts.

What happens if substring length is longer than the string?

A Returns an error

B Returns substring up to string end

C Returns empty string

D Returns full string twice

It returns the substring from start position to the end without error.

Explain how to extract a part of a string using the substring function in PostgreSQL.

Think about specifying where to start and how many characters to take.

Describe how the overlay function works and give an example.

It’s like cutting out a piece and putting a new piece in its place.