Imagine a university database that stores student names, departments, email addresses, and other text information.
Sometimes we need to modify or manipulate this text.
For example:
PostgreSQL provides String Functions to perform these operations.
A string is a sequence of characters.
Examples:
Ali
Computer Science
Ghazi University
ali@gmail.com
Pakistan
In PostgreSQL, strings are commonly stored using these data types:
| Data Type | Description | Example |
|---|---|---|
CHAR(n) |
Fixed-length character string | CHAR(10) |
VARCHAR(n) |
Variable-length string with a maximum length | VARCHAR(50) |
TEXT |
Variable-length string without a declared limit | TEXT |
Example:
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(50),
department TEXT
);
We will use a students table to understand string functions.
CREATE TABLE students (
student_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(100),
email VARCHAR(100)
);
INSERT INTO students
VALUES
(1, 'Ali', 'Khan', 'Computer Science', 'ali@gmail.com'),
(2, 'Sara', 'Ahmed', 'Mathematics', 'sara@gmail.com'),
(3, 'Usman', 'Raza', 'Information Technology', 'usman@gmail.com'),
(4, 'Hina', 'Malik', 'Physics', 'hina@gmail.com'),
(5, 'Bilal', 'Ahmad', 'Chemistry', 'bilal@gmail.com');
SELECT *
FROM students;
Expected output:
| student_id | first_name | last_name | department | |
|---|---|---|---|---|
| 1 | Ali | Khan | Computer Science | ali\@gmail.com |
| 2 | Sara | Ahmed | Mathematics | sara\@gmail.com |
| 3 | Usman | Raza | Information Technology | usman\@gmail.com |
| 4 | Hina | Malik | Physics | hina\@gmail.com |
| 5 | Bilal | Ahmad | Chemistry | bilal\@gmail.com |
The UPPER() function converts all characters in a string into uppercase letters.
UPPER(string);
SELECT UPPER('Ghazi University');
Expected output:
GHAZI UNIVERSITY
SELECT
first_name,
UPPER(first_name) AS uppercase_name
FROM students;
Expected output:
| first_name | uppercase_name |
|---|---|
| Ali | ALI |
| Sara | SARA |
| Usman | USMAN |
| Hina | HINA |
| Bilal | BILAL |
The LOWER() function converts all characters in a string into lowercase letters.
LOWER(string);
SELECT LOWER('GHAZI UNIVERSITY');
Expected output:
ghazi university
SELECT
first_name,
LOWER(first_name) AS lowercase_name
FROM students;
The INITCAP() function converts the first letter of each word into uppercase and the remaining letters into lowercase.
INITCAP(string);
SELECT INITCAP('ghazi university');
Expected output:
Ghazi University
SELECT INITCAP('COMPUTER SCIENCE');
Expected output:
Computer Science
SELECT
department,
INITCAP(department) AS formatted_department
FROM students;
The LENGTH() function returns the number of characters in a string.
LENGTH(string);
SELECT LENGTH('Pakistan');
Expected output:
| length |
|---|
| 8 |
SELECT
first_name,
LENGTH(first_name) AS name_length
FROM students;
Expected output:
| first_name | name_length |
|---|---|
| Ali | 3 |
| Sara | 4 |
| Usman | 5 |
| Hina | 4 |
| Bilal | 5 |
Note: Spaces are also counted as characters.
SELECT LENGTH('Ghazi University');
Expected output:
16
The CONCAT() function combines two or more strings into one string.
CONCAT(string1, string2, ...);
SELECT CONCAT('Ghazi', ' University');
Expected output:
Ghazi University
SELECT
CONCAT(first_name, ' ', last_name) AS full_name
FROM students;
Expected output:
| full_name |
|---|
| Ali Khan |
| Sara Ahmed |
| Usman Raza |
| Hina Malik |
| Bilal Ahmad |
CONCAT_WS() combines strings using a specified separator.
WS stands for With Separator.
CONCAT_WS(separator, string1, string2, ...);
SELECT CONCAT_WS('-', '2026', '11', '09');
Expected output:
2026-11-09
SELECT
CONCAT_WS(' ', first_name, last_name) AS full_name
FROM students;
| CONCAT() | CONCAT_WS() |
|---|---|
| Combines strings directly | Combines strings with a separator |
| Separator must be included manually | Separator is provided once |
CONCAT('Ali', 'Khan') |
CONCAT_WS(' ', 'Ali', 'Khan') |
Important: CONCAT_WS() skips NULL values after the separator. CONCAT() also treats NULL arguments as empty strings.
The SUBSTRING() function extracts a specific part of a string.
SUBSTRING(string FROM start_position FOR length);
string: Original text.start_position: Position where extraction begins.length: Number of characters to extract.SELECT SUBSTRING('PostgreSQL' FROM 1 FOR 4);
Expected output:
Post
SELECT SUBSTRING('PostgreSQL' FROM 5 FOR 3);
Expected output:
gre
SELECT
first_name,
SUBSTRING(first_name FROM 1 FOR 2) AS short_name
FROM students;
Expected output:
| first_name | short_name |
|---|---|
| Ali | Al |
| Sara | Sa |
| Usman | Us |
| Hina | Hi |
| Bilal | Bi |
The LEFT() function returns a specified number of characters from the beginning of a string.
LEFT(string, number_of_characters);
SELECT LEFT('PostgreSQL', 4);
Expected output:
Post
SELECT
first_name,
LEFT(first_name, 3) AS short_name
FROM students;
The RIGHT() function returns a specified number of characters from the end of a string.
RIGHT(string, number_of_characters);
SELECT RIGHT('PostgreSQL', 3);
Expected output:
SQL
SELECT
email,
RIGHT(email, 9) AS email_part
FROM students;
For extracting the complete domain regardless of its length, use a more suitable function such as SPLIT_PART(), which we will study later.
The TRIM() function removes unwanted characters from the beginning, end, or both sides of a string.
It is commonly used to remove extra spaces.
TRIM(string);
SELECT TRIM(' Ghazi University ');
Expected output:
Ghazi University
SELECT LTRIM(' Pakistan');
Expected output:
Pakistan
SELECT RTRIM('Pakistan ');
Expected output:
Pakistan
| Function | Purpose |
|---|---|
TRIM() |
Removes characters from both ends |
LTRIM() |
Removes characters from the left |
RTRIM() |
Removes characters from the right |
The REPLACE() function replaces all occurrences of a specified substring with another substring.
REPLACE(string, old_text, new_text);
SELECT REPLACE(
'Hello World',
'World',
'Pakistan'
);
Expected output:
Hello Pakistan
SELECT
REPLACE(
department,
'Computer Science',
'CS'
) AS short_department
FROM students;
This replaces Computer Science with CS.
The POSITION() function returns the starting position of a substring within another string.
POSITION(substring IN string);
SELECT POSITION('SQL' IN 'PostgreSQL');
Expected output:
8
SELECT
email,
POSITION('@' IN email) AS at_position
FROM students;
This identifies the position of the @ character in each email address.
STRPOS() also returns the position of a substring within a string.
STRPOS(string, substring);
SELECT STRPOS('PostgreSQL', 'SQL');
Expected output:
8
Note: Both POSITION() and STRPOS() perform substring position searches. They return 0 if the substring is not found.
The REVERSE() function reverses the order of characters in a string.
REVERSE(string);
SELECT REVERSE('Pakistan');
Expected output:
natsikaP
SELECT
first_name,
REVERSE(first_name) AS reversed_name
FROM students;
The REPEAT() function repeats a string a specified number of times.
REPEAT(string, number);
SELECT REPEAT('SQL ', 3);
Expected output:
SQL SQL SQL
LPAD() adds characters to the left side of a string until it reaches the specified length.
LPAD(string, length, fill_text);
SELECT LPAD('25', 5, '0');
Expected output:
00025
SELECT
student_id,
LPAD(student_id::TEXT, 5, '0') AS formatted_id
FROM students;
Expected output:
| student_id | formatted_id |
|---|---|
| 1 | 00001 |
| 2 | 00002 |
| 3 | 00003 |
| 4 | 00004 |
| 5 | 00005 |
Here, ::TEXT converts the integer student ID into text.
RPAD() adds characters to the right side of a string until it reaches the specified length.
RPAD(string, length, fill_text);
SELECT RPAD('SQL', 8, '*');
Expected output:
SQL*****
| LPAD() | RPAD() |
|---|---|
| Adds characters to the left | Adds characters to the right |
LPAD('25', 5, '0') |
RPAD('25', 5, '0') |
00025 |
25000 |
The SPLIT_PART() function divides a string using a specified delimiter and returns a selected part.
SPLIT_PART(string, delimiter, field_number);
SELECT SPLIT_PART('Ali Khan', ' ', 1);
Expected output:
Ali
SELECT SPLIT_PART('Ali Khan', ' ', 2);
Expected output:
Khan
SELECT
email,
SPLIT_PART(email, '@', 2) AS email_domain
FROM students;
Expected output:
| email_domain | |
|---|---|
| ali\@gmail.com | gmail.com |
| sara\@gmail.com | gmail.com |
| usman\@gmail.com | gmail.com |
| hina\@gmail.com | gmail.com |
| bilal\@gmail.com | gmail.com |
STRING_AGG() combines values from multiple rows into a single string.
It is particularly useful for reports.
STRING_AGG(column_name, separator);
SELECT
department,
STRING_AGG(first_name, ', ') AS student_names
FROM students
GROUP BY department;
Expected output:
| department | student_names |
|---|---|
| Computer Science | Ali, Bilal |
| Mathematics | Sara |
| Information Technology | Usman |
| Physics | Hina |
| Chemistry | Bilal |
The exact order of names is not guaranteed unless ordering is specified inside STRING_AGG().
For predictable ordering:
SELECT
department,
STRING_AGG(first_name, ', ' ORDER BY first_name) AS student_names
FROM students
GROUP BY department;
String functions can also be used to filter records.
SELECT *
FROM students
WHERE UPPER(first_name) = 'ALI';
This finds Ali even if the stored name has different letter casing.
SELECT *
FROM students
WHERE first_name LIKE 'A%';
SELECT *
FROM students
WHERE email LIKE '%gmail%';
SELECT *
FROM students
WHERE LENGTH(first_name) > 4;
UPPER() converts text into uppercase.LOWER() converts text into lowercase.INITCAP() capitalizes the first letter of each word.LENGTH() counts characters.CONCAT() combines strings.CONCAT_WS() combines strings using a separator.SUBSTRING() extracts part of a string.LEFT() extracts characters from the beginning.RIGHT() extracts characters from the end.TRIM() removes unwanted characters from both ends.REPLACE() replaces specified text.POSITION() finds the position of a substring.REVERSE() reverses a string.LPAD() adds characters to the left.RPAD() adds characters to the right.SPLIT_PART() extracts a part of a string using a delimiter.STRING_AGG() combines values from multiple rows.Computer Science with CS.@ in each email address.gmail.com.GU-.| Function | Purpose |
|---|---|
UPPER() |
Converts text to uppercase |
LOWER() |
Converts text to lowercase |
INITCAP() |
Capitalizes each word |
LENGTH() |
Counts characters |
CONCAT() |
Combines strings |
CONCAT_WS() |
Combines strings with a separator |
SUBSTRING() |
Extracts part of a string |
LEFT() |
Extracts characters from the left |
RIGHT() |
Extracts characters from the right |
TRIM() |
Removes characters from both ends |
LTRIM() |
Removes characters from the left |
RTRIM() |
Removes characters from the right |
REPLACE() |
Replaces specified text |
POSITION() |
Finds substring position |
STRPOS() |
Finds substring position |
REVERSE() |
Reverses a string |
REPEAT() |
Repeats a string |
LPAD() |
Adds characters to the left |
RPAD() |
Adds characters to the right |
SPLIT_PART() |
Extracts a delimited part |
STRING_AGG() |
Combines values from multiple rows |
Next Topic: Mathematical Functions in PostgreSQL
Students will learn how to perform mathematical calculations using ROUND(), CEIL(), FLOOR(), ABS(), POWER(), SQRT(), MOD(), and other useful mathematical functions.