Right Function for Postgres SQL

Introduction

In this article, you will learn to use the Right function in Postgres SQL to achieve various text- or string-related tasks. This article will discuss the following:

  • What? What does the RIGHT function do?
  • How? How to use the Right() function in PostgreSQL queries and with what parameters.
  • Why? When do we need this text (string) function in our Postgres SQL?
  • Extras We’ll also use various other SQL commands and functions, including SELECT, FROM, WHERE, CHAR_LENGTH(), UPPER(), and LEFT().

Prerequisites

  • High level understanding of how to write SQL queries with Postgres (or MS SQL Server, MySQL, Oracle, etc.), using one of PostgreSQL’s free administration tools, another RDB admin tool, or via code or script languages like PHP, Python, Javascript, Java, C#, ASP.Net, VB.Net, Note.js, Ruby, etc. that allows a connection to your database as well as a method for sending SQL commands or requests, to query or make changes to your database.
  • Knowledge of the use of the most basic SQL statements, including SELECT, FROM, and WHERE.
  • Understanding of the meaning of the following terms: text data types, string variable types, and concatenation.

What functions does RIGHT fulfill in Postgres SQL?

PostgreSQL’s RIGHT function is used to retrieve a number of characters starting from the right side of source text.

How to use this SQL function (with parameters)

Syntax of RIGHT() function

t_string_new = RIGHT(t_string_source, i_number_characters_to_use);

Above, we gave the RIGHT function two parameters. A breakdown:

  • t_string_new: This is the string we get after using the function.
  • t_string_sourse: The string we feed to our RIGHT function.
  • i_number_characters_to_use: Tells RIGHT() how many characters, beginning from the right, and starting at “1”, to return.

Using the syntax above and going a bit further:

t_string_source = "I find Postgres' right() function to be very useful in my manipulation of strings";
i_number_characters_to_use = 7;
t_string_new = RIGHT(t_string_source, i_number_characters_to_use);

The above SQL script begins at the right side of our source string, grabs 7 characters, and assigns the result to _t_stringnew. So t_string_new’s value becomes “strings”.

Now to use the Right() function in a relational database context, in the WHERE clause:

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

t_namet_categories
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

We want the contents of the tcategories 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”.
SELECT
    CHAR_LENGTH(t_categories) AS i_cat_full_length
    , LEFT(t_categories, i_cat_full_length - 5) -- 5 is the length of the word ("Best")
FROM
    databases_and_languages
WHERE
    RIGHT(t_categories, 5) = ' Best';

Analysis:

  • SELECT CHAR_LENGTH: For simplicity and ease of learning, we are setting “i_cat_full_length” to use the CHAR_LENGTH function to determine the full length of the text in the “t_categories” column.
  • SELECT LEFT: Using the above “i_cat_full_length” variable, we’re using the LEFT function to use the text from “t_categories” but chopping off the last 5 characters, which happen to be ” Best”.
  • FROM: This tells PostgreSQL we want to use the “databases_and_languages” table to get our data from.
  • WHERE RIGHT: Here we are using the RIGHT function to limit the recordset based on rows needing to have the last 5 characters in the t_category column be equal to ” Best”.

Here’s what we get when we execute that SQL above: (Leaving out the “i_cat_full_length” temp column)

t_namet_categories
PostgreSQLDatabase Relational Free
PythonLanguage Newish Flexible

Another example:

Our table is named “technologies”:

t_namet_category
OracleRDB
MongoNoSQL
MySQLRDB
PostgreSQLRDB
PythonLanguage
PHPLanguage

Let’s write some SQL to query our data:

SELECT
  UPPER(LEFT(t_name,1))
  || LOWER(RIGHT(t_name,CHAR_LENGTH(t_name)-1))
  -- CHAR_LENGTH(item) returns the character length of our string, which is the text value of t_name for the given row.
  -- RIGHT(item, length) returns the length again; number of characters of t_name, starting from the right.

  || ' - '
  -- The '||' symbol combines strings, acting the same as the CONCAT() function would. Here we add a space, then dash, then another space to the string we're building.

  || UPPER(LEFT(t_category,1))
  || LOWER(RIGHT(t_category,CHAR_LENGTH(t_category)-1)) AS t_results
  -- Here we have done the same thing to t_category that we did to t_name above.
FROM technologies

Which returns this recordset:

t_namet_results
OracleRdb
MongoNosql
MysqlRdb
PostgresqlRdb
PythonLanguage
PhpLanguage

Miscellaneous Notes

  • In some situations – like with arrays – we begin the count at zero. So it is important to keep in mind that with the RIGHT() function, we begin counting positions at one, not zero.
  • Did you notice we named some fields, columns, and variables with a prefix of “i” or “t“? In this article, we designated “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

In this article we learned how to use the RIGHT() function in our Postgres SQL. We also practiced the use of SELECT, FROM, WHERE, UPPER, CHAR_LENGTH, and LEFT SQL statements and functions.

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.