Working with Strings: LENGTH(), SUBSTRING(), TRIM(), POSITION(), REPLACE(), INITCAP()

When you’re dealing with names, addresses, emails, or any other text — you almost always need to do one of these things:

• Extract part of a string
• Find out the length
• Clean up extra spaces
• Find/replace a fragment
• Format the output

For example, you might need to:

• Show only the last name from a full name.
• Find students whose last name starts with "P".
• Replace all spaces with underscores.
• Show the last name with a capital letter.

All these tasks are super easy with PostgreSQL string functions.

LENGTH() — string length

The LENGTH() function returns the number of characters in a string.

Syntax:

LENGTH(string)

Example:

SELECT name, LENGTH(name) AS name_length
FROM students;

Super useful for checking the length of full names, passwords, and other text stuff.

SUBSTRING() — extracting part of a string

Lets you grab a substring from a string — like the first 3 letters of a last name or the domain from an email.

Syntax:

SUBSTRING(string FROM start FOR length)

• start — position of the first character (starts at 1)
• length — how many characters to extract

Example 1: first 3 letters of a last name

SELECT last_name, SUBSTRING(last_name FROM 1 FOR 3) AS prefix
FROM students;

Example 2: email domain

SELECT email, SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain
FROM students;

TRIM() — removing extra spaces

Removes spaces (and other characters) at the beginning and/or end of a string.

Syntax:

TRIM([LEADING | TRAILING | BOTH] chars FROM string)

But most of the time you just use:

TRIM(string)

Example:

SELECT '[' || TRIM('   Art   ') || ']' AS cleaned;

Super important if users are entering data by hand (like with spaces at the start).

POSITION() — finding a substring

Returns the position where a substring starts.

Syntax:

POSITION(substring IN string)

Example:

SELECT email, POSITION('@' IN email) AS at_position
FROM students;

You can use it with SUBSTRING() to extract parts of a string.

REPLACE() — replacing a substring

Replaces all occurrences of one substring with another.

Syntax:

REPLACE(string, from_substring, to_substring)

Example: replace spaces with underscores

SELECT name, REPLACE(name, ' ', '_') AS fixed_name
FROM students;

INITCAP() — capitalize first letter

Transforms a string so that the first letter of each word is uppercase, the rest are lowercase.

Syntax:

INITCAP(string)

Example:

SELECT INITCAP('anna pal') AS full_name;

Handy for standardizing full names.