Right Function for Postgres SQL
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
- 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
WHERE, CHAR_LENGTH(), UPPER(), and LEFT().
- Knowledge of the use of the most basic SQL statements, including
- Understanding of the meaning of the following terms: text data types, string variable types, and concatenation.
What functions does RIGHT fulfill in Postgres SQL?
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
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:
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
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:
|PostgreSQL||Database Relational Free Best|
|Oracle||Database Relational Monolithic|
|Mongo||Database NoSQL Flexible|
|MySQL||Database Relational Free|
|Python||Language Newish Flexible Best|
|PHP||Language Old Pervasive|
|Java||Language Powerful 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”.
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")
RIGHT(t_categories, 5) = ' Best';
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)
|PostgreSQL||Database Relational Free|
|Python||Language Newish Flexible|
Our table is named “technologies”:
Let’s write some SQL to query our data:
-- 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.
|| 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.
Which returns this recordset:
- 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.
In this article we learned how to use the
RIGHT() function in our Postgres SQL. We also practiced the use of
LEFT SQL statements and functions.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started