PostgreSQL SUBSTRING() String Function

PostgreSQL SUBSTRING() String Function

The PostgreSQL SUBSTRING() function is used to extract a part of a string, starting from a specified position and optionally for a specified length. This function is essential for text manipulation and data extraction tasks.

---

Syntax

SUBSTRING(string FROM start [FOR length])

The SUBSTRING() function has the following components:

• string: The string from which to extract the substring.
• start: The starting position for extraction.
• length: The number of characters to extract (optional).

---

Example PostgreSQL SUBSTRING() Queries

Let's look at some examples of PostgreSQL SUBSTRING() function queries:

1. Basic SUBSTRING() Example

SELECT SUBSTRING('Hello, World!' FROM 8 FOR 5) AS substring;

This query extracts a substring starting from position 8 and of length 5 from the string 'Hello, World!', resulting in 'World'.

2. SUBSTRING() Without Specified Length

SELECT SUBSTRING('Hello, World!' FROM 8) AS substring;

This query extracts a substring starting from position 8 to the end of the string 'Hello, World!', resulting in 'World!'.

3. SUBSTRING() with Column Values

SELECT id, name, SUBSTRING(name FROM 2 FOR 3) AS substring
FROM users;

This query retrieves the id, name, and the substring starting from position 2 and of length 3 from the name for each row in the users table.

---

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the SUBSTRING() function to extract parts of text data.

Step 1: Creating a Table

This step involves creating a new table named users to store user data.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT
);

In this example, we create a table named users with columns for id and name.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the users table.

INSERT INTO users (name)
VALUES ('Alice'),
       ('Bob'),
       ('Charlie');

Here, we insert data into the users table.

Step 3: Using the SUBSTRING() Function

This step involves using the SUBSTRING() function to extract parts of the text data in the users table.

Basic SUBSTRING()

SELECT SUBSTRING('Hello, World!' FROM 8 FOR 5) AS substring;

This query extracts a substring from the string 'Hello, World!' starting at position 8 and with a length of 5 characters.

SUBSTRING() Without Specified Length

SELECT SUBSTRING('Hello, World!' FROM 8) AS substring;

This query extracts a substring from the string 'Hello, World!' starting at position 8 to the end of the string.

SUBSTRING() with Column Values

SELECT id, name, SUBSTRING(name FROM 2 FOR 3) AS substring
FROM users;

This query extracts a substring from the values in the 'name' column of the 'users' table starting at position 2 and with a length of 3 characters.

These queries demonstrate how to use the SUBSTRING() function to extract parts of the text data in the users table, including basic usage and handling column values.

Conclusion

The PostgreSQL SUBSTRING() function is a fundamental tool for text manipulation and data extraction by extracting a part of a string. Understanding how to use the SUBSTRING() function and its syntax is essential for effective text data manipulation in PostgreSQL databases.