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.