Left Function in Postgres SQL

Introduction

Here we learn in this tutorial session to use the Left function to achieve various string-related tasks in Postgres SQL, including:

• What? What does the LEFT function do?
• How? How do we use the Left() function in our PostgreSQL queries? What parameters does it use?
• Why? When do we need this text function in our queries?
• Extras We’ll also get exposure to basic SQL commands including SELECT, FROM, and WHERE.

Prerequisites

• Basic knowledge of how to create and use SQL queries with Postgres (or MS SQL Server, MySQL, Oracle, etc.), using PostgreSQL’s free admin tool or another relational database administration 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 it SQL commands or requests, to query or make changes to a database.
• Knowledge of the use of basic SQL (PL/SQL) statements, including SELECT, FROM, and WHERE.
• Understanding of what the following terms mean: text types, strings, and concatenation.

What does the LEFT function do in Postgres SQL?

We use PostgreSQL’s LEFT function to isolate a specific number of characters starting from the left part of a string (text).

How to use this function with SQL and parameters

Syntax of LEFT() function

Notice, we gave the LEFT function two parameters. Here is an explanation:

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

Using the syntax above and going a bit further:

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

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

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

• t_name_function
• the first character from t_category_function
• the first three characters from t_operates_on

Analysis:

• SELECT t_name_function: The name of the function.
• LEFT(t_category_function, 1): In this line of our SQL code we ask for the first character in the “t_category_function” column using 1 for the i_number_characters_to_use parameter.
• LEFT(t_operates_on, 3): In this part we use the i_number_characters_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:

Let’s try another mini-project to refine our understanding of how the LEFT function works in Postgres SQL:

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

Analysis:

• SELECT t_name_tech: The name of the function.
• LEFT(t_category_tech, 5): In this line of our SQL code we ask for the first five characters in the “t_category_tech” column using 5 for the “i_number_characters_to_use” parameter.
• FROM: Telling PostgreSQL to pull our data from “tbl_technologies”.

The above query returns the following data set:

How about a slightly more complex example in SQL of how LEFT can be used. In this project, we have a similar table called “databases_and_languages”. We want to get a specific part of text from the “t_categories” column. Here is the table with data:

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”.

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)

Miscellaneous Notes

• 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.
• 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

In this tutorial lesson we gained experience points in how to use the LEFT() function in our Postgres SQL. We also practiced the use of SELECT, FROM, WHERE, CHAR_LENGTH, and RIGHT SQL statements and functions.