String length and position functions in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to get the length of the string 'hello'.

PostgreSQL

SELECT [1]('hello');

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

Alength

Bposition

Csubstr

Dchar_length

Attempts:

3 left

💡 Hint

Common Mistakes

Using position instead of length.

Using substr which extracts part of a string.

The length function returns the number of characters in a string in PostgreSQL.

2fill in blank

medium

Complete the code to find the position of 'a' in the string 'banana'.

PostgreSQL

SELECT [1]('a' IN 'banana');

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

Astrpos

Blength

Cposition

Dchar_length

Attempts:

3 left

💡 Hint

Common Mistakes

Using length instead of position.

Using strpos which is similar but different syntax.

The position function returns the location of a substring inside a string in PostgreSQL.

3fill in blank

hard

Fix the error in the code to correctly find the position of 'na' in 'banana'.

PostgreSQL

SELECT position([1] IN 'banana');

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

A'banana'

B'na'

C"na"

Dna

Attempts:

3 left

💡 Hint

Common Mistakes

Not using quotes around the substring.

Using double quotes which are for identifiers.

The substring must be a string literal enclosed in single quotes in PostgreSQL.

4fill in blank

hard

Fill both blanks to get the length of 'apple' and find the position of 'p' in it.

PostgreSQL

SELECT [1]('apple') AS len, [2]('p' IN 'apple') AS pos;

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

Alength

Bposition

Cchar_length

Dstrpos

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up length and char_length.

Using strpos instead of position.

length returns the string length, and position finds the substring position.

5fill in blank

hard

Fill all three blanks to create a query that returns a dictionary with the uppercase word as key, its length as value, and only include words where length is greater than 3.

PostgreSQL

SELECT jsonb_object_agg([1], [2]) FROM (SELECT UPPER(word) AS [1], [2] FROM words WHERE [3]) sub;

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

Aword

Blength(word)

Clength(word) > 3

DUPPER(word)

Attempts:

3 left

💡 Hint

Common Mistakes

Using word instead of UPPER(word) for keys.

Not filtering words by length.

Using wrong syntax for filtering.

We use UPPER(word) as the key, length(word) as the value, and filter with length(word) > 3.

Practice

1. What does the LENGTH function do in PostgreSQL?

easy

A. Splits a string into an array

B. Finds the position of a substring inside a string

C. Converts a string to uppercase

D. Returns the number of characters in a string

Solution

1. Step 1: Understand LENGTH function purpose

   The LENGTH function counts how many characters are in a given string.

2. Step 2: Compare with other options

   POSITION finds substring location, UPPER changes case, SPLIT divides string. Only LENGTH counts characters.

3. Final Answer:

   Returns the number of characters in a string -> Option D

4. Quick Check:

   LENGTH = character count [OK]

Hint: LENGTH counts characters, POSITION finds substring start [OK]

Common Mistakes:

• Confusing LENGTH with POSITION
• Thinking LENGTH changes string case
• Assuming LENGTH splits strings

2. Which of the following is the correct syntax to find the position of substring 'cat' in string 'concatenate' using PostgreSQL?

easy

A. LENGTH('cat' IN 'concatenate')

B. POSITION('concatenate' IN 'cat')

C. POSITION('cat' IN 'concatenate')

D. FIND('cat', 'concatenate')

Solution

1. Step 1: Recall POSITION syntax

   POSITION(substring IN string) finds where substring starts in string.

2. Step 2: Check each option

   POSITION('cat' IN 'concatenate') matches syntax. POSITION('concatenate' IN 'cat') reverses arguments. LENGTH('cat' IN 'concatenate') uses LENGTH wrongly. FIND('cat', 'concatenate') uses non-existent FIND function.

3. Final Answer:

   POSITION('cat' IN 'concatenate') -> Option C

4. Quick Check:

   POSITION(substring IN string) correct syntax [OK]

Hint: Use POSITION('sub' IN 'string') to find substring start [OK]

Common Mistakes:

• Swapping substring and string arguments
• Using LENGTH instead of POSITION
• Using non-PostgreSQL functions like FIND

3. What is the result of this query?

SELECT POSITION('day' IN 'holiday') AS pos, LENGTH('holiday') AS len;

medium

A. pos = 4, len = 7

B. pos = 5, len = 7

C. pos = 0, len = 7

D. pos = 4, len = 6

Solution

1. Step 1: Find POSITION of 'day' in 'holiday'

   'holiday' positions: 1:'h', 2:'o', 3:'l', 4:'i', 5:'d', 6:'a', 7:'y'. 'day' matches positions 5('d')-6('a')-7('y'), so starts at 5 (1-based).

2. Step 2: Calculate LENGTH of 'holiday'

   'holiday' has 7 characters.

3. Final Answer:

   pos = 5, len = 7 -> Option B

4. Quick Check:

   POSITION('day' IN 'holiday') = 5, LENGTH('holiday') = 7 [OK]

Hint: Count characters carefully; POSITION is 1-based [OK]

Common Mistakes:

• Counting POSITION starting at 0
• Miscounting substring start
• Confusing LENGTH with POSITION

4. Identify the error in this query:

SELECT POSITION('test', 'contest') AS pos;

medium

A. Uses comma instead of IN keyword in POSITION function

B. Missing AS keyword for alias

C. POSITION does not accept string literals

D. Syntax is correct, no error

Solution

1. Step 1: Check POSITION function syntax

   POSITION requires syntax POSITION(substring IN string), not POSITION(substring, string).

2. Step 2: Identify error in query

   The query uses comma instead of IN keyword, causing syntax error.

3. Final Answer:

   Uses comma instead of IN keyword in POSITION function -> Option A

4. Quick Check:

   POSITION(substring IN string) correct syntax [OK]

Hint: Use IN keyword, not comma, in POSITION function [OK]

Common Mistakes:

• Using comma instead of IN in POSITION
• Assuming alias AS is mandatory (it's optional)
• Thinking POSITION can't use string literals

5. You want to find the position of the first space character in the string 'Find the position'. Which query will correctly return this position?

hard

A. SELECT POSITION(' ' IN 'Find the position');

B. SELECT LENGTH(' ' IN 'Find the position');

C. SELECT POSITION('space' IN 'Find the position');

D. SELECT POSITION(' ' FROM 'Find the position');

Solution

1. Step 1: Identify correct function usage for substring position

   POSITION(substring IN string) finds where substring starts. We want the space character ' '.

2. Step 2: Evaluate each option

   SELECT POSITION(' ' IN 'Find the position'); uses correct syntax and substring. SELECT LENGTH(' ' IN 'Find the position'); uses LENGTH wrongly. SELECT POSITION('space' IN 'Find the position'); searches for word 'space' which is not present. SELECT POSITION(' ' FROM 'Find the position'); uses FROM instead of IN, which is invalid syntax.

3. Final Answer:

   SELECT POSITION(' ' IN 'Find the position'); -> Option A

4. Quick Check:

   POSITION(' ' IN string) finds first space [OK]

Hint: Use POSITION(' ' IN string) to find first space [OK]

Common Mistakes:

• Using LENGTH instead of POSITION
• Searching for word 'space' instead of character
• Using FROM instead of IN in POSITION