String length and position functions in PostgreSQL - Step-by-Step Execution

Concept Flow - String length and position functions

Start with input string

↓

Apply length function

→Get number of characters

↓

Apply position function with substring

↓

Get position of substring in string

↓

Return results

We start with a string, then use length() to find its size and position() to find where a substring appears.

Execution Sample

PostgreSQL

SELECT length('hello world') AS len, position('world' IN 'hello world') AS pos;

This query finds the length of 'hello world' and the position where 'world' starts.

Execution Table

Step	Function	Input	Evaluation	Result
1	length	'hello world'	Count characters	11
2	position	'world' IN 'hello world'	Find substring start	7
3	Return	len=11, pos=7	Output results	(11, 7)

💡 Both functions evaluated and results returned.

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
input_string	'hello world'	'hello world'	'hello world'	'hello world'
len	NULL	11	11	11
pos	NULL	NULL	7	7

Key Moments - 2 Insights

Why does position('world' IN 'hello world') return 7 and not 6?

Because position counts starting at 1, so 'w' in 'world' is the 7th character in 'hello world' (see execution_table step 2).

Does length() count spaces as characters?

Yes, length() counts every character including spaces, so 'hello world' length is 11 (execution_table step 1).

Visual Quiz - 3 Questions

Look at the execution_table, what is the length of 'hello world' at step 1?

A 12

B 11

C 10

D 7

💡 Hint

Check the 'Result' column in execution_table row with Step 1.

At which step does the position function find the substring?

A Step 3

B Step 1

C Step 2

D Position function is not used

💡 Hint

Look at the 'Function' column in execution_table to find when position() is evaluated.

If the substring 'world' was not found, what would position() return?

A 0

B -1

C NULL

D 1

💡 Hint

In PostgreSQL, position() returns 0 if substring is not found.

Concept Snapshot

length(string) returns the number of characters including spaces.
position(substring IN string) returns the 1-based index of substring or 0 if not found.
Both functions help analyze string content.
Counting starts at 1, not 0.
Use SELECT to get these values in queries.

Full Transcript

This lesson shows how to use PostgreSQL string functions length() and position(). We start with a string 'hello world'. The length() function counts all characters including spaces and returns 11. The position() function finds where the substring 'world' starts in the string, returning 7 because counting starts at 1. If the substring is not found, position() returns 0. These functions help us understand string size and substring location in databases.

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