PostgreSQL String Functions: Practical SQL Guide for Beginners

-


PostgreSQL string functions help you clean text, search for specific patterns, combine values, and transform data for reporting. They are useful for data analysts, SQL developers, and database administrators.

Sample Table

Assume an employees table contains these records.

employee_id employee_name department
1 Rahul Naidu IT
2 Priya Sharma HR
3 Arjun Kumar Finance

1. UPPER() and LOWER()

UPPER() converts text to uppercase, while LOWER() converts text to lowercase.

SELECT
    employee_name,
    UPPER(employee_name) AS uppercase_name,
    LOWER(employee_name) AS lowercase_name
FROM employees;

These functions are useful for standardizing text.

2. LENGTH()

LENGTH() returns the number of bytes in a string. For ordinary ASCII text, this matches the character count.

SELECT
    employee_name,
    LENGTH(employee_name) AS name_length
FROM employees;

For counting characters in multibyte text, use CHAR_LENGTH().

3. CONCAT()

CONCAT() combines multiple values into one string.

SELECT CONCAT(employee_name, ' - ', department) AS employee_details
FROM employees;

You can also use the concatenation operator ||.

SELECT employee_name || ' works in ' || department AS employee_details
FROM employees;

4. TRIM(), LTRIM(), and RTRIM()

These functions remove unwanted characters from the beginning or end of a string. By default, they remove spaces.

SELECT TRIM('   PostgreSQL   ') AS cleaned_text;

Remove leading spaces:

SELECT LTRIM('   PostgreSQL') AS cleaned_text;

Remove trailing spaces:

SELECT RTRIM('PostgreSQL   ') AS cleaned_text;

These functions are useful when cleaning imported datasets.

5. REPLACE()

REPLACE() replaces every occurrence of a specified substring.

SELECT REPLACE('Learn SQL Online', 'Online', 'Today') AS updated_text;

Expected result: Learn SQL Today

6. SUBSTRING()

SUBSTRING() extracts part of a string.

SELECT SUBSTRING('PostgreSQL' FROM 1 FOR 4) AS extracted_text;

Expected result: Post

7. LEFT() and RIGHT()

LEFT() extracts characters from the beginning, while RIGHT() extracts characters from the end.

SELECT
    LEFT('PostgreSQL', 4) AS first_four,
    RIGHT('PostgreSQL', 3) AS last_three;

Expected result: Post and SQL

8. POSITION()

POSITION() finds the location of a substring within a string.

SELECT POSITION('SQL' IN 'PostgreSQL') AS position_value;

Expected result: 8

If the substring is not found, the function returns zero.

9. SPLIT_PART()

SPLIT_PART() divides a string using a delimiter and returns the requested part.

SELECT SPLIT_PART('Rahul,IT,60000', ',', 2) AS department;

Expected result: IT

This can be useful when processing consistently formatted text fields.

10. CONCAT_WS()

CONCAT_WS() combines values using a separator.

SELECT CONCAT_WS(' ', 'Rahul', 'Naidu') AS full_name;

Expected result: Rahul Naidu

Unlike CONCAT(), CONCAT_WS() skips NULL arguments after the separator.

Common Mistakes to Avoid

  1. Forgetting that string positions start at 1 in PostgreSQL.
  2. Assuming LENGTH() always equals the number of visible characters.
  3. Using string functions to clean data without checking the original values.
  4. Forgetting that POSITION() returns zero when a substring is not found.

Practice Questions

  1. Convert every employee name to uppercase.
  2. Find the length of each employee name.
  3. Remove leading and trailing spaces from a text column.
  4. Replace spaces in a department label with underscores.
  5. Extract the first three characters of every employee name.
  6. Combine employee name and department into one column.
  7. Find the position of the letter a in each employee name.
  8. Extract the second part of a comma-separated string using SPLIT_PART().

Interview Questions

1. What is the difference between UPPER() and LOWER()?

UPPER() converts text to uppercase, while LOWER() converts it to lowercase.

2. What is the purpose of TRIM()?

It removes specified characters from the beginning and end of a string. By default, it removes spaces.

3. What is the difference between CONCAT() and CONCAT_WS()?

CONCAT() joins values directly, while CONCAT_WS() joins values using a separator and skips NULL arguments after that separator.

4. What does SUBSTRING() do?

It extracts a portion of a string.

5. What does POSITION() return?

It returns the starting position of a substring, or zero if the substring is not found.

Conclusion

PostgreSQL string functions are important for cleaning, transforming, and analyzing text data. Practise these functions with real datasets to improve your SQL skills and prepare for data analyst interviews.

Comments

Post a Comment

Popular posts from this blog

PostgreSQL Tutorial for Beginners: Learn SQL with Practical Examples

-
Introduction PostgreSQL is a powerful, open-source relational database management system used to store, organize, and retrieve data. It is widely used in web applications, business software, reporting systems, and data analytics projects. In this tutorial, you will learn the fundamentals of PostgreSQL through practical examples. You will understand how to create tables, insert records, retrieve data, filter results, and summarize information using SQL queries. This guide is designed for beginners who want to develop practical database skills for software development, data analytics, and other technology-related careers. What You Will Learn By the end of this tutorial, you will understand: What PostgreSQL is and how it works. How to create a database table. How to insert and retrieve records. How to filter and sort data. How to summarize data using aggregate functions. How to practice SQL using a business-related example. What Is PostgreSQL? PostgreSQL is an open-source relational datab...
Read more

PostgreSQL Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK and NOT NULL – Practical SQL Guide

-
Constraints are rules applied to table columns to maintain data accuracy and consistency in a PostgreSQL database. They prevent invalid data from being inserted or updated and help maintain relationships between tables. In this tutorial, you will learn the most commonly used PostgreSQL constraints with practical SQL examples. What Are Constraints in PostgreSQL? Constraints define rules that table data must follow. For example: - An employee ID should be unique. - An employee name should not be NULL. - A salary should not be negative. - An order should reference an existing customer. PostgreSQL provides several constraints to enforce these rules. 1. NOT NULL Constraint The NOT NULL constraint ensures that a column cannot contain NULL values. Suppose you have an employees table. CREATE TABLE employees (     employee_id INT,     employee_name VARCHAR(100) NOT NULL,     department VARCHAR(50) ); In this example, employee_name cannot contain NULL values. Insert ...
Read more