Sling Academy
Home/PostgreSQL/Using LEFT and RIGHT String Functions in PostgreSQL

Using LEFT and RIGHT String Functions in PostgreSQL

Last updated: January 05, 2024

Introduction

The LEFT and RIGHT functions in PostgreSQL are part of a suite of string functions that programmers can use to manipulate strings in PostgreSQL databases. They allow developers to extract substrings from a field by specifying the number of characters to retrieve starting from the left or right of the string respectively. These functions are incredibly useful in data transformation, cleaning, and preparation tasks.

History and Purpose

The LEFT and RIGHT functions have been part of PostgreSQL and several other SQL databases for many years. As part of SQL’s standard string functions, they have been immensely helpful in string manipulation when querying relational databases. Their primary purpose is to simplify the extraction of specific parts of a string based on position, making it easier to analyze or transform data directly within database queries.

Syntax, Parameters, and Return Value

The basic syntax for the LEFT and RIGHT functions in PostgreSQL is as follows:

LEFT(string, number_of_characters)
RIGHT(string, number_of_characters)

Parameters:

  • string: The text string whose leftmost or rightmost characters you want to extract. This can be a field name or a literal string.
  • number_of_characters: This is a positive integer that dictates how many characters from the start (left) or end (right) of the text string will be extracted.

The return value is a substring of the input text string, containing the specified number of leftmost or rightmost characters.

Code Examples

Example 1: Extracting a Substring from a User’s Full Name

Assuming you have a table with a column ‘full_name’, we will use the LEFT function to extract the user’s first name assuming the first name is always the first five characters.

-- Code snippet: Extracting first name
SELECT LEFT(full_name, 5) AS first_name
FROM users;

Example 2: Retrieving File Extensions

In a table containing a list of file paths, you can use the RIGHT function to get the file extension assuming a 3-character extension following a dot.

-- Code snippet: Retrieving file extensions
SELECT RIGHT(file_path, 3) AS file_extension
FROM files;

Example 3: Formatting Phone Numbers

Consider a scenario where phone numbers are stored with country codes and you only need the last 10 digits.

-- Code snippet: Formatting phone numbers
SELECT RIGHT(phone_number, 10) AS local_number
FROM contacts;

Example 4: Creating Usernames

Summary: When creating a username from the user’s email address, we can use the LEFT function up to the ‘@’ character.

-- Code snippet: Creating usernames
SELECT LEFT(email, POSITION('@' IN email) - 1) AS username
FROM accounts;

Conclusion

In conclusion, LEFT and RIGHT functions in PostgreSQL are simple yet powerful tools for string manipulation directly within SQL queries. They are particularly useful for parsing structured text data, enabling data analysts and developers to extract relevant information from a larger string. These functions enhance SQL’s versatility for data processing and play a crucial role in day-to-day database operations.

Next Article: Using REVERSE String Function in PostgreSQL

Previous Article: PostgreSQL String Functions: UPPER, LOWER, and INITCAP

Series: PostgreSQL Tutorials: From Basic to Advanced

PostgreSQL

You May Also Like

  • PostgreSQL with TimescaleDB: Querying Time-Series Data with SQL
  • PostgreSQL Full-Text Search with Boolean Operators
  • Filtering Stop Words in PostgreSQL Full-Text Search
  • PostgreSQL command-line cheat sheet
  • How to Perform Efficient Rolling Aggregations with TimescaleDB
  • PostgreSQL with TimescaleDB: Migrating from Traditional Relational Models
  • Best Practices for Maintaining PostgreSQL and TimescaleDB Databases
  • PostgreSQL with TimescaleDB: Building a High-Performance Analytics Engine
  • Integrating PostgreSQL and TimescaleDB with Machine Learning Models
  • PostgreSQL with TimescaleDB: Implementing Temporal Data Analysis
  • Combining PostgreSQL, TimescaleDB, and Airflow for Data Workflows
  • PostgreSQL with TimescaleDB: Visualizing Real-Time Data with Superset
  • Using PostgreSQL with TimescaleDB for Energy Consumption Analysis
  • PostgreSQL with TimescaleDB: How to Query Massive Datasets Efficiently
  • Best Practices for Writing Time-Series Queries in PostgreSQL with TimescaleDB
  • PostgreSQL with TimescaleDB: Implementing Batch Data Processing
  • Using PostgreSQL with TimescaleDB for Network Traffic Analysis
  • PostgreSQL with TimescaleDB: Troubleshooting Common Performance Issues
  • Building an IoT Data Pipeline with PostgreSQL and TimescaleDB