Postgres Text Functions

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

We’ll study the most commonly used Postgres text functions here, including Left, Right, Char_length, and Replace. We’ll look at each of these text manipulation functions for Postgres through the lense of SQL (structured query language) that is specific to PostgreSQL.

Prerequisites

  • Some knowledge of Postgres SQL query creation. We like to use DBeaver, which can be helpful in writing and debugging queries for PostgreSQL and other popular database systems.
  • Understanding of what the following terms mean: text data types, string data types, and string concatenation.

We’ll begin with – arguably – the most simple and perhaps oft-used of Postgres text-related functions for string manipulation; Left().

Postgres Left function

We use the Left function from Postgres to return a specific number of characters starting from the left part of a string of characters; “text”.

Postgres Left function syntax

1
t_text_new = LEFT(t_text_source, i_num_chars_to_get);

Notice, we input two parameters to the LEFT function. Explanation:

  • t_text_new: This is the string we get after using the LEFT function.
  • t_text_sourse: The string we feed to our function.
  • i_num_chars_to_get: Tells LEFT() how many characters, starting from the left, and starting at “1”, to return.

Using the syntax above and going a bit further:

Postgres Left function example

1
2
3
t_text_source = "I intend to become an expert with the use of the Postgres left() function."
i_num_chars_to_use = 6
t_text_new = LEFT(t_text_source, i_num_chars_to_use)

The above SQL script takes the 6 left-most characters from the contents of “t_text_source” and assigns the result to t_text_new. So now t_text_new’s value is “I want”.

Let’s now use the Left() function in a database context:

Postgres Left function with SQL

t_name_functiont_cat_of_functiont_operates_on
leftSingularstring
rightSingularstring
avgAggregatenumber
countAggregateall
minAggregatenumber
maxAggregatenumber
sumAggregatenumber

Our goal: From the table above, we want the following data returned:

  • t_name_function
  • the first character from t_cat_of_function
  • the first three characters from t_operates_on
1
2
3
4
5
6
SELECT
    t_name_function
    , LEFT(t_cat_of_function, 1)
    , LEFT(t_operates_on, 3)
FROM
    tbl_functions;

Analysis:

  • SELECT t_name_function: The name of the function in a given row of our PostgreSQL table of functions.
  • LEFT(t_cat_of_function, 1): In this line of our script we look at the 1st character in the “t_cat_of_function” column using 1 for the i_num_chars_to_use parameter.
  • LEFT(t_operates_on, 3): In this part we use the i_num_chars_to_use parameter to return the first three characters of text in the t_operates_on column.
  • FROM: Telling Postgres which table we are getting our data from; “tbl_data”.

The Postgres SQL above returns:

t_name_functiont_cat_of_functiont_operates_on
leftSstr
rightSstr
avgAnum
countAall
minAnum
maxAnum
sumAnum

Postgres Left function SQL exercise

Let’s try another exercise to increase understanding of how the Postgres LEFT function works with SQL:

t_name_techt_cat_of_tech
PythonLanguage
JavaLanguage
MongoNoSQL
PostgreSQLDatabase
PHPLanguage
JavascriptLanguage

Goal: Return the first 5 characters of the string (text) in the “t_cat_of_tech” field from a table called “tbl_techs”.

1
2
3
4
5
SELECT
    t_name_tech
    , LEFT(t_cat_of_tech, 5)
FROM
    tbl_techs

Analysis:

  • SELECT t_name_tech: The name we gave that function for that record in the database.
  • LEFT(t_cat_of_tech, 5): In this line of our PostgreSQL query script, we retrieve the first 5 characters in the “t_cat_of_tech” column using five for the “i_num_chars_to_use” parameter.
  • FROM: Instructing Postgres on which table to get data from, which happens to be “tbl_techs”.

That SQL returns the following recordset from PostgreSQL:

t_name_techt_cat_of_tech
PythonLangu
JavaLangu
MongoNoSQL
PostgreSQLDatab
PHPLangu
JavascriptLangu

More Postgres Left function examples

How about a slightly more complex example in SQL of how we can use the RIGHT function for Postgres. In this exercise, we have a similar table called “tbl_databases_n_languages”. We want to retrieve a distinct part of text from the “t_cats” column. Here is the table with data:

t_namet_cats
PythonLanguage Newish Flexible Best
PostgreSQLDatabase Relational Free Best
OracleDatabase Relational Monolithic
MongoDatabase NoSQL Flexible
PHPLanguage Old Pervasive
JavaLanguage Powerful Pervasive
JavaScriptLanguage Easy Pervasive

Getting the contents of the t_cats field without the last word if that word happens to be “Best”. Let’s make a plan:

  • (1) Get the data as a text.
  • (2) Filter to find if “Best” is at the end of that text.
  • (3) Get the length of that data.
  • (4) Return the left side of that data minus the length of ” Best”.
1
2
3
4
5
6
7
SELECT
    CHAR_LENGTH(t_cats) AS i_cat_full_length
    , LEFT(t_cats, i_cat_full_length - 5) -- 5 is the length of the word ("Best")
FROM
    databases_and_languages
WHERE
    RIGHT(t_cats, 5) = ' Best';

Analysis:

  • CHAR_LENGTH: For ease of learning, we set “i_cat_full_length” to use the PostgreSQL CHAR_LENGTH function to find out the length of the full string of text in the “t_cats” column.
  • SELECT LEFT: Using the above “i_cat_full_length” variable, we use the PostgreSQL LEFT function to use the string from “t_cats” while deleting the last 5 characters, which happen to be ” Best”.
  • WHERE RIGHT: Here we use RIGHT to filter the recordset based on records that need to have the last 5 characters in the t_cats column be ” Best”.

Here’s what we get when we execute the above Postgres query:

t_namet_cats
PythonLanguage Newish Flexible
PostgreSQLDatabase Relational Free

Postgres Left start count

In some situations, like with arrays, we start counting at zero. So it is important to keep in mind that with LEFT() and RIGHT() we begin counting positions at the value of one, not zero.

Postgres Right Function

Postgres’ RIGHT function is used to retrieve a number of characters starting at the right side of the source text you supply. Let’s dive in.

Postgres Right function syntax

1
t_text_new = RIGHT(t_text_source, i_num_chars_to_use);

Above, we gave the Postgres RIGHT function two parameters. Analysis:

  • t_text_new: This is the text we get after using the Right() function.
  • t_text_sourse: The text we feed to the PostgreSQL RIGHT function.
  • i_num_chars_to_use: Conveys how many characters to the RIGHT function, beginning from the right, and starting at 1.

Going a bit further:

Postgres Right function example

1
2
3
t_my_text_source = "I like Postgres' right() function and find it to be very useful in my manipulation of text";
i_num_chars_to_use = 5;
t_text_new = RIGHT(t_my_text_source, i_num_chars_to_use);

The above Postgres query begins at the right side of the source text, retrieves 5 characters, and assigns the result to t_text_new. So t_text_new now has a value of ” text”.

Right function from a Postgres query

Now to use the Postgres Right function in a relational database context, as part of the WHERE clause:

See the “tbl_databases_and_languages” table below. Our goal is to get a specific part of text from the “t_cats” column. Here is the table with data:

t_namet_cats
PostgreSQLDatabase Relational Free Best
OracleDatabase Relational Monolithic
MongoDatabase NoSQL Flexible
MySQLDatabase Relational Free
PythonLanguage Newish Flexible Best
PHPLanguage Old Pervasive
JavaLanguage Powerful Pervasive
JavaScriptLanguage Easy Pervasive

Our mission: Get the t_cats field value but without the last word when that word happens to be “Best”:

  • (1) Get the text value.
  • (2) Filter to look for “Best” at the end.
  • (3) Calculate the length of that string.
  • (4) Receive the left side of that text without the number of characters we calculated above, which comes to the length of ” Best”.
1
2
3
4
5
6
7
SELECT
    CHAR_LENGTH(t_cats) AS i_cat_full_length
    , LEFT(t_cats, i_cat_full_length - 5) -- 5 is the length of the word ("Best")
FROM
    tbl_databases_and_languages
WHERE
    RIGHT(t_cats, 5) = ' Best';

Analysis

  • CHAR_LENGTH: For ease of cranial integration, we set “i_cat_full_length” to use Postgres’ CHAR_LENGTH function to calculate the number of characters of the text in the “t_cats” column.
  • SELECT LEFT: Using the above “i_cat_full_length” variable, we’re using the LEFT function to use the text from “t_cats” but removing the last 5 characters, which happen to be ” Best”.
  • FROM: This tells PostgreSQL we use the “tbl_databases_and_languages” Postgres table to get our data from.
  • WHERE RIGHT: Here we utilize the RIGHT function to limit the recordset returned based on records needing to have the last 5 characters in t_cats be equal to ” Best”.

And when we execute that query:

t_namet_cats
PostgreSQLDatabase Relational Free
PythonLanguage Newish Flexible

Another example:

We named the PostgreSQL table below as “tbl_techs”:

t_namet_cats
OracleRDB
MongoNoSQL
MySQLRDB
PostgreSQLRDB
PythonLanguage
PHPLanguage

Let’s write some SQL to query our data:

1
2
3
4
5
6
7
SELECT
  UPPER(LEFT(t_name,1))
  || LOWER(RIGHT(t_name, CHAR_LENGTH(t_name)-1))
  || ' - '
  || UPPER(LEFT(t_cats,1))
  || LOWER(RIGHT(t_cats,CHAR_LENGTH(t_cats)-1)) AS t_results
FROM tbl_techs

PostgreSQL uses Upper, Lower, Left, Right, and Char_Length to return this recordset:

t_results
Oracle – Rdb
Mongo – Nosql
Mysql – Rdb
Postgresql – Rdb
Python – Language
Php – Language

Now let’s move on to a study of how to use the Replace text function.

Postgres Replace function

As you will see below, we use the Replace function in Postgres to change one text string to another, within any given string.

Postgres Replace function syntax

1
t_text_result := REPLACE(t_text_source, t_text_to_replace, t_text_replacement)

Parameters:

  • t_text_source: The text to search through to find and replace a substring within that source text.
  • t_text_to_replace: The string we are looking for that is somewhere within the source string.
  • t_text_replacement: The string we want to use to replace the above “string to replace”.

Postgres Replace function example

1
2
3
4
5
6
DECLARE
    t_text_source := "Choose a five liter Mustang or a Tesla Model 3. Either way, it's FAST.";
    t_text_to_replace := "Either way";
    t_text_replacement := "Whatever the case";
SELECT
    REPLACE(t_text_source, t_text_to_replace, t_text_replacement);

The result will be “Choose a five liter Mustang or a Tesla Model 3. Whatever the case, it’s FAST.”

Postgres REPLACE function uses

We’ll start with using REPLACE to change some text in a Postgres database. Our goal is to replace all instances of “They will” in the database with “They’ll”. Let’s show you how this works in a string we’re building using Python in order to update a PostgreSQL database table.

1
2
3
4
s = ""
s += "UPDATE tbl_comments"
s += " SET"
s += " t_comment = REPLACE(t_comment, "They will", "They'll";)

Analysis of the above code

  • UPDATE: Setting the source data table to be “tbl_comments”.
  • SET: This is where we change the value in “t_comment”. We’re instructing Postgres “If you find “They will” anywhere within this column, replace the string with “They’ll”.”
  • WHERE: Constraining to update so that only the text phrase of “They will” is replaced with the text we built above.

The Postgres query, when executed, changes the data in this table

t_name_usert_comment
TedWe will
BifThey will
SusanI will
TimWe will
GaryWe will
SammyThey will
FriedaThey will

to become

t_name_usert_comment
TedWe will
BifThey’ll
SusanI will
TimWe will
GaryWe will
SammyThey’ll
FriedaThey’ll

Notice that every case of “They will” was been changed to “They’ll”?

Other PostgreSQL Text Functions

Some honorable mentions we didn’t cover here: Substring, Lpad, Splitting a string into an array, and more. That said, we did make some use of more than just Left, Right, Upper, Lower, Char_length, and Replace in this tutorial.

Miscellaneous Notes

  • Did you notice we named some columns and variables with a prefix of “i” or “t“? In this tutorial, we used “i” to mean integer and “t” to mean text or string. Here is a short tutorial that explains why in detail, including how the practice can increase your efficiency as a programmer.

Conclusion

We learned details on how to use the most common Postgres text functions in this tutorial, including Left, Right, Char_length, and Replace. In future articles, we’ll cover others.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.