Basic String Functions

Strings are one of the most common data types you will work with in any database. PostgreSQL provides a rich set of built-in functions for inspecting, transforming, and combining text data. Mastering these functions will let you clean messy data, build formatted output, and perform text searches directly inside your SQL queries.

Measuring Strings with LENGTH

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

SELECT LENGTH('PostgreSQL');
-- Returns: 10

You can use it on table columns to find out how long stored values are:

SELECT name, LENGTH(name) AS name_length
FROM employees
ORDER BY name_length DESC;

This is useful for data validation -- for example, finding names that are suspiciously short or long:

SELECT name, LENGTH(name) AS name_length
FROM employees
WHERE LENGTH(name) < 5;

Changing Case with UPPER and LOWER

UPPER converts every character to uppercase. LOWER converts every character to lowercase.

SELECT UPPER('hello world');
-- Returns: 'HELLO WORLD'

SELECT LOWER('HELLO WORLD');
-- Returns: 'hello world'

These functions are essential for case-insensitive comparisons. Instead of worrying about whether a user typed "Sales", "sales", or "SALES", you can normalize the case:

SELECT name, department
FROM employees
WHERE LOWER(department) = 'sales';

You can also use INITCAP to capitalize the first letter of each word:

SELECT INITCAP('john doe');
-- Returns: 'John Doe'

Removing Whitespace with TRIM

TRIM removes leading and trailing whitespace (or other specified characters) from a string.

SELECT TRIM('   hello   ');
-- Returns: 'hello'

You can also trim specific characters and control which side to trim from:

-- Remove leading zeros
SELECT TRIM(LEADING '0' FROM '000123');
-- Returns: '123'

-- Remove trailing dots
SELECT TRIM(TRAILING '.' FROM 'hello...');
-- Returns: 'hello'

-- Remove both sides
SELECT TRIM(BOTH '*' FROM '***important***');
-- Returns: 'important'

PostgreSQL also provides shorthand functions:

SELECT LTRIM('   hello');   -- Trims left side only: 'hello'
SELECT RTRIM('hello   ');   -- Trims right side only: 'hello'

Extracting Parts with SUBSTRING

SUBSTRING extracts a portion of a string. You specify the starting position and optionally the length.

-- SUBSTRING(string FROM start FOR length)
SELECT SUBSTRING('PostgreSQL' FROM 1 FOR 8);
-- Returns: 'PostgreS'

SELECT SUBSTRING('PostgreSQL' FROM 8);
-- Returns: 'SQL'

You can also use the more concise comma-separated syntax:

-- SUBSTRING(string, start, length)
SELECT SUBSTRING('PostgreSQL', 1, 8);
-- Returns: 'PostgreS'

A practical example -- extracting the domain from email addresses:

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

Combining Strings with CONCAT and ||

There are two ways to concatenate strings in PostgreSQL.

The CONCAT function:

SELECT CONCAT('Hello', ' ', 'World');
-- Returns: 'Hello World'

The || operator:

SELECT 'Hello' || ' ' || 'World';
-- Returns: 'Hello World'

The key difference is how they handle NULL values. CONCAT treats NULL as an empty string, while || propagates NULL:

SELECT CONCAT('Hello', NULL, 'World');
-- Returns: 'HelloWorld'

SELECT 'Hello' || NULL || 'World';
-- Returns: NULL

Building a formatted output from employee data:

SELECT CONCAT(name, ' works in ', department) AS employee_info
FROM employees;

Or using the || operator:

SELECT name || ' (' || department || ')' AS employee_info
FROM employees;

There is also CONCAT_WS (concat with separator), which joins strings with a delimiter:

SELECT CONCAT_WS(', ', name, department, email) AS full_info
FROM employees;
-- Returns something like: 'Alice Johnson, Engineering, alice@example.com'

Replacing Text with REPLACE

REPLACE substitutes all occurrences of a substring with another string.

SELECT REPLACE('Hello World', 'World', 'PostgreSQL');
-- Returns: 'Hello PostgreSQL'

Practical uses include cleaning data:

-- Remove all dashes from a string
SELECT REPLACE('123-456-7890', '-', '');
-- Returns: '1234567890'

-- Standardize department names
SELECT REPLACE(department, 'Dept', 'Department') AS full_department
FROM employees;

Note that REPLACE is case-sensitive. REPLACE('Hello', 'hello', 'hi') would return 'Hello' unchanged because the cases do not match.

Finding Substrings with POSITION

POSITION returns the location of a substring within a string. It returns 0 if the substring is not found.

SELECT POSITION('SQL' IN 'PostgreSQL');
-- Returns: 8

SELECT POSITION('xyz' IN 'PostgreSQL');
-- Returns: 0

This is often combined with SUBSTRING to extract dynamic portions of text:

-- Extract the username part of an email
SELECT
  email,
  SUBSTRING(email FROM 1 FOR POSITION('@' IN email) - 1) AS username
FROM employees;

You can also use STRPOS, which takes the arguments in a different order:

SELECT STRPOS('PostgreSQL', 'SQL');
-- Returns: 8

Combining Functions

The real power comes from combining multiple string functions in a single query:

SELECT
  UPPER(SUBSTRING(name FROM 1 FOR 1)) || LOWER(SUBSTRING(name FROM 2)) AS formatted_name,
  LENGTH(name) AS name_length,
  POSITION(' ' IN name) AS space_position
FROM employees;

Here is an example that creates a standardized employee code:

SELECT
  UPPER(SUBSTRING(name FROM 1 FOR 3)) || '-' || UPPER(SUBSTRING(department FROM 1 FOR 3)) AS employee_code,
  name,
  department
FROM employees;

Try It Yourself

1. Write a query that returns each employee's name and their email domain (the part after the @ sign). Use SUBSTRING and POSITION.

2. Write a query that concatenates each customer's name and city, separated by " - ", and shows the total length of the result. Use CONCAT or || together with LENGTH.

3. Find all customers whose name contains more than 10 characters. Display the name and its length.

4. For each employee, display their name in all lowercase and their department in all uppercase.