An Overview of SQL Text Functions in PostgreSQL

If you’re a PostgreSQL user and into data analysis, you’ve heard of the text functions or maybe even used them. But what are PostgreSQL’s most popular text functions? In today’s article, we’ll show you what they are and how to use them.

As anyone who works with them knows, databases don’t contain only numerical values. Among other data types, databases also store text or string data. To use PostgreSQL effectively, you’ll have to know at least some of Postgres’ text functions.

The best way to learn text functions is our interactive Common Functions in PostgreSQL course. It deals with the text functions we’ll cover in this article, such as CONCAT(), LENGTH(), LOWER(), UPPER(), INITCAP(), TRIM(), LTRIM(), RTRIM(), SUBSTRING(), and REVERSE(). The course is not limited only to the text function, but it also teaches you numeric, date and time, and other functions. To make sure what you learn sticks, you’ll have to go through 143 interactive courses to complete the course.

You can also review standard SQL text functions, which are available in most SQL databases. If you want to review other types of SQL functions, I recommend our free  Standard SQL Functions cheat sheet.

But what is the function itself?

Functions in SQL

The word ‘function’ comes from the Latin word ‘functio’, which means ‘performance’ or ‘execution’.

In SQL, a function is a named and predefined piece of code that you can use in an SQL query. The function is executed based on the argument, i.e. the instructions written in the function’s parentheses.

General function syntax ( used here in a SELECT statement ) looks like this:

SELECT POSTGRES_FUNCTION(argument1, argument 2, …)
FROM table;

Example Data

We’ll show you how the string functions work using data from the imaginary company Überhip. The table uberhip_employees contains some data about the company’s employees.

• id – The employee’s ID and the table's primary key (PK).
• nin – The employee’s national identification number.
• first_name – The employee’s first name.
• last_name – The employee’s last name.
• email – the employee’s company email.
• job_title – The employee’s job title.
• department – The employee’s department.

The table has only five records, but it’ll be enough to show you how these functions work.

If you want to use this table as we go through the examples, use the code below to create it. If you don’t have PostgreSQL installed on your computer, here are the instructions on how to install Postgres in 5 minutes.

CREATE TABLE uberhip_employees (
	id INT, 
	NIN VARCHAR (50),
	first_name VARCHAR (50),
	last_name VARCHAR (50),
	email VARCHAR (50),
	job_title VARCHAR (50),
	department VARCHAR (50)
);

INSERT INTO uberhip_employees (id, NIN, first_name, last_name, email, job_title, department) VALUES (1, '49754679811', 'Gino', 'Cioccolatino', 'gcioccolatino@uberhip.com', 'team leader', 'Accounting');

INSERT INTO uberhip_employees (id, NIN, first_name, last_name, email, job_title, department) VALUES (2, '15975947554', 'Sara', 'Gitarra', 'sgitarra@uberhip.com', 'senior associate', 'Accounting');

INSERT INTO uberhip_employees (id, NIN, first_name, last_name, email, job_title, department) VALUES (3, '45978416432', 'Kado', 'Advocado', 'kadvocado@uberhip.com', 'senior associate', 'Accounting');

INSERT INTO uberhip_employees (id, NIN, first_name, last_name, email, job_title, department) VALUES (4, '97415974951', 'Bobby', 'Snobby', 'bsnobby@uberhip.com', 'junior associate', 'Accounting');

INSERT INTO uberhip_employees (id, NIN, first_name, last_name, email, job_title, department) VALUES (5, '69759649822', 'Gilbert', 'Camembert', 'gcamembert@uberhip.com', 'junior associate', 'Accounting');

Let’s now go directly to the examples.

PostgreSQL Text Functions

The II Operator

While this is not a function itself, it replaces one: CONCAT(). It’s a function used for merging two or more strings into one.

Here’s how this operator works in a query.

SELECT first_name,
	 last_name,
	 job_title,
	 first_name || ' ' || last_name || ', ' || job_title AS employee_and_job_title
FROM uberhip_employees;

We wanted to show the employees name and their job title in one cell using this query. You can achieve that by using the || operator.

Between each string, you need to put the || operator. In string data, whitespace is also a character, so you have to explicitly state where you want it. In our case, the first and the last name are separated by the whitespace, and they are then separated from the job title by a comma and the whitespace.

Here’s how the output looks.

The result looks good, so we can go to the next example.

CHAR_LENGTH(), CHARACTER_LENGTH() and BIT_LENGTH()

The CHAR_LENGTH and CHARACTER_LENGTH() functions are exactly the same because they return the length of a string in the number of characters.

Can you think what measurement BIT_LENGTH() uses to return the text length? Yes, it’s bits! Here’s a query that uses all three functions so that we can compare the output:

SELECT nin,  
	 first_name,
	 last_name,
	 CHAR_LENGTH(nin) AS nin_char_length,
	 CHARACTER_LENGTH(nin) AS nin_character_length,
	 BIT_LENGTH(nin) AS nin_bit_length
FROM uberhip_employees;

These are the first real functions we’re using. The argument in all three functions is the string or a column whose length we want to calculate.

This is the result:

According to both CHAR_LENGTH() and CHARACTER_LENGTH(), the NIN for every employee is 15 characters long.

Let’s see what BIT_LENGTH() returns. It says that all the NINs are 120 bits long, except for the one, which is 128 bits long. If the string has 15 characters, the 120 bits length means every character takes up 8 bits.

But why is one NIN 8 bits longer even though it has 15 characters? If you inspect it closer, you’ll see that where the fourth digit should be, there’s something that doesn’t quite belong to these NINs – a Cyrillic letter ‘d’. The NIN in question is 128 bits long, which means this Cyrillic letter takes up not 8 but 16 bits.

UPPER(), LOWER() & INITCAP()

The UPPER() and LOWER() functions are different sides of the same coin. UPPER() turns all the letters into uppercase. LOWER() does the opposite; all the letters become lowercase.

INITCAP() is, in a way, a combination of both. The first letter of every word is converted to uppercase, while all other letters become lowercase.

Here’s how you can use UPPER() and LOWER():

SELECT first_name,
	 last_name,
	 department,
	 UPPER(department) AS department_uppercase,
	 LOWER(department) AS department_lowercase
FROM uberhip_employees;

The same logic for writing functions is used here: write the column you want to convert in the parentheses and the letter cases will be changed.

In this case, we expect to see one column with the department name written in all uppercase and the other in all lowercase. Let’s check if this will happen.

The output is as we expected!

And now, take a look at what INITCAP() will do. This time, we’re using the column job_title as the function argument:

SELECT first_name,
	 last_name,
	 job_title,
	 INITCAP(job_title) AS job_position_formatted
FROM uberhip_employees;

The said column is written all in lowercase, but after applying the INITCAP() function, it will be formatted better:

Let’s now move on to the next PostgreSQL text function.

SUBSTRING()

The SUBSTRING() function will return a defined smaller part (a substring) of a string. When we say defined, it means we can define the position within the string where the substring starts. We can also define the length of the substring we want to output.

Imagine that you want to show only one part of the column nin.

SELECT first_name,
	 last_name,
	 nin,
	 SUBSTRING(nin, 7, 3) AS nin_substring
FROM uberhip_employees;

The function’s first argument is the string itself. The second argument is an integer (7), telling the function that the seventh character of a string will be the first character of the substring. The third argument is also an integer; 3 in this case. It defines the length of a substring, which is three characters in our example.

Let’s take a look at the code output.

You can count manually to check the result. For example, the first six characters in Gino Cioccolatino’s NIN are xx4975. Starting from the seventh character and in a total length of three characters, the substring is 467.

REPLACE()

One of the popular text functions in PostgreSQL is REPLACE(). This is a function that replaces defined characters with other characters. The replacement occurs in the query output, not the original dataset itself.

In our example, the Überhip company renamed its Accounting department to Accounting & Finance. Let’s use the REPLACE() function to update the database records:

SELECT first_name,
	 last_name,
	 department,
	 REPLACE(department, 'Accounting', 'Accounting & Finance') AS department_renamed
FROM uberhip_employees;

The column we want to rename is the first argument in the function. The second argument is the string we want to replace; in our case, it’s ‘Accounting’.  The third argument defines what the previous string will be replaced with (here, ‘Accounting & Finance’).

Executing the code will show the renamed department:

TRIM()

The TRIM() function is generally used for cleaning data because it removes the specified characters from the start, end, or start and end of a string. A typical usage example is if your data has trailing or leading zeroes and you want to remove them.

We will show a similar example here. You probably noticed that the nin column has two X’s on both sides of the values. We’re going to clean that data with the following code:

SELECT first_name,
	 last_name,
 nin,
	 TRIM('x' FROM nin) AS nin_trimmed
FROM uberhip_employees;

The syntax is a little different from other functions we’ve shown, but it’s still based on writing arguments in the parentheses.

First, you need to define the character you want to remove. In our case, it’s the letter ‘x’. Then you need to specify from which text you want to remove this string. Here, we’re using the keyword FROM and specifying the column nin.

Have a look at how it cleans data:

REVERSE()

This is a function that takes the string and turns it the other way round. Its first character becomes last, and the second becomes the second last, and so on. In other words, the string’s beginning becomes its end and vice versa.

Let’s use this function on the nin column. Imagine that, for some reason, the system shows the NIN data from the last digit to the first. If you know the REVERSE() function, it won’t be difficult for you to repair this.

SELECT first_name,
	 last_name,
	 nin,
	 REVERSE(TRIM('x' FROM nin)) AS nin_reversed
FROM uberhip_employees;

This is also a good opportunity to show you how two text functions can be combined. We want to reverse the NIN values, but we also want to show them without the leading and trailing x. So we want to reverse the trimmed version of NIN. First, we should write the TRIM() function, which we already learned how to do.

The REVERSE() function is simple; it only needs the string it should reverse. In our case, it is the whole TRIM() function with its arguments. And here are the results:

POSITION()

The POSITION() function finds the location of a specified substring and returns its position within the string. Its position is shown as the number of characters from the string’s beginning.

Let’s see how this works if we want to find the position of ‘@’ in the employees’ emails.

SELECT first_name,
	 last_name,
	 email,
	 POSITION('@' IN email) AS position_at
FROM uberhip_employees;

When writing the function, first define the character you want to look for. Then use the keyword IN to specify the column in which you want this character to be found.

Let’s check this in the first row. If you count from the left, there are 13 characters in Mr. Cioccolatino’s email. The character ‘@’ is in the fourteenth position.

Are There More PostgreSQL Text Functions? It’s Up to You to Find Out! 

We’ve covered only several text functions in PostgreSQL –  albeit the most popular ones. Use this article as a springboard. You can always come back to it, look up the function you’re looking for, and study the example to remember how it works.

But there are many more functions that can be equally useful. It only depends on your interest and your job requirements. 

You can find some of them and practice their use in the interactive Common Functions in PostgreSQL course. It contains 143 hands-on exercises to let you practice text, numeric, and other functions in PostgreSQL.