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

t_string_new = LEFT(t_string_source, i_number_characters_to_use);

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:

t_string_source = "I intend to become an expert with the use of the Postgres left() function."
i_number_characters_to_use = 6
t_string_new = LEFT(t_string_source, i_number_characters_to_use)

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:

t_name_functiont_category_functiont_operates_on
char_lengthSingularstring
lengthSingularstring
lowerSingularstring
upperSingularstring
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_category_function
  • the first three characters from t_operates_on
SELECT
    t_name_function
    , LEFT(t_category_function, 1)
    , LEFT(t_operates_on, 3)
FROM
    tbl_functions

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:

t_name_functiont_category_functiont_operates_on
char_lengthSstr
lengthSstr
lowerSstr
upperSstr
leftSstr
rightSstr
avgAnum
countAall
minAnum
maxAnum
sumAnum

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

t_name_techt_category_tech
JavaLanguage
MongoNoSQL
MySQLDatabase
PostgreSQLDatabase
PythonLanguage
PHPLanguage
JavascriptLanguage

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

SELECT
    t_name_tech
    , LEFT(t_category_tech, 5)
FROM
    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:

t_name_techt_category_tech
JavaLangu
MongoNoSQL
MySQLDatab
PostgreSQLDatab
PythonLangu
PHPLangu
JavascriptLangu

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:

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

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.

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.