Trim Function in Postgres SQL

Introduction

In this tutorial we use the Trim function in Postgres SQL to achieve various text related mini-projects, including:

  • What? What’s the syntax of the TRIM function and what does the function do? How do we use the Trim() function in our PostgreSQL query commands? What parameters does it use?
  • Why? When do we need this function in our SQL?
  • Extras We’ll get exposure to various SQL statements including SELECT, FROM, and WHERE. We’ll work some with the ARRAY variable type, the ARRAY_LENGTH function, the WHILE loop, RAISE NOTICE, and how to CAST an integer as text.

Prerequisites

  • Knowledge of how to write SQL in Postgres, using PostgreSQL’s free database admin tool or another relational database administration tool like DBeaver, a powerful and free GUI for adding extra ease and control. Or with programming languages like Python, PHP, Javascript, C#, Java, ASP.Net, VB.Net, Note.js, Ruby, etc. that provide a database connection as well as a method for sending SQL compatible commands to query or make changes to a Postgres database.
  • Knowledge of the use of basic SQL statements, including SELECT, FROM, and WHERE.
  • Basic awareness of how the following functions work: REPEAT, ddd, and ddd.
  • Understanding of what the following terms mean: text types, strings, and integers.
  • Optional but important: Tutorial on naming conventions that explains why we prefix our variables, column names, table names, etc. (all objects) as you see us doing here. For example, naming “tphrase_original” with the “t” you see at the beginning in order to delineate it as a “text” object.

What does the TRIM function do in Postgres SQL and what is the syntax?

Put simply, we use PostgreSQL’s TRIM function to remove spaces. Used without any parameters,

Syntax of the TRIM() function

t_result = TRIM([LEADING OR TRAILING OR BOTH] [optional: t_string_to_remove] FROM t_string_source);

We give the TRIM function these parameters:

  • LEADING: Optional. Tells Postgres to begin at the start of t_string_source and move forward.
  • TRAILING: Optional. Tells Postgres to begin at the end of t_string_source and move backward.
  • BOTH: Optional. Tells Postgres to remove the t_string_to_remove character from both the beginning and end of t_string_source.
  • t_string_to_remove: Optional. Supplies TRIM() with an optional string to look for. If not supplied, space (chr(32)) is assumed.
  • t_string_source: Mandatory parameter. The string we feed to the function. This is the only parameter required.

Examples with parameters

  • trim(LEADING FROM " Which is your favorite database? ") returns “Which is your favorite database? “
  • trim(TRAILING FROM " Which is your favorite database? ") returns ” Which is your favorite database?”
  • trim(BOTH FROM " Which is your favorite database? ") returns “Which is your favorite database?”
  • trim(" Which is your favorite database? ") returns “Which is your favorite database?” Yes, same as using “BOTH FROM”.

How to use the Trim function with an Array

Let’s use the syntax we just learned and apply the TRIM function to every item in an array. We’ll make use of the WHILE LOOP to accomplish this task.

-- variable declarations
DECLARE
    a_t_technologies TEXT[4];
    a_t_technologies := {" Python ", " Postgres ", " MS SQL ", " Javascript "};
    t_string_to_remove TEXT := " ";
    i_current_array_spot INTEGER := 0;
    i_size_of_array INTEGER := ARRAY_LENGTH(a_t_technologies,1); -- this is 4
BEGIN
-- loop through our four phrases
    WHILE i_current_array_spot < i_size_of_array
        LOOP
            i_current_array_spot := i_current_array_spot + 1;
            t_string_source := a_t_technologies[i_current_array_spot]
            t_result := TRIM(BOTH t_string_to_remove FROM t_string_source);
            RAISE NOTICE t_result;
        END LOOP;
END

Analysis

  • DECLARE: In this area we (a) Initialize and fill the “a_t_technologies” array with four items. (b) For “t_string_to_remove”, we chose a space. (c) We are setting “i_current_array_spot” to be an integer of zero to get our loop started. This will be used to count through the WHILE LOOP, pick the word in our array we are currently working on, and then END LOOP. (d) We use “i_size_of_array” in our loop below to tell Postgres when to leave the loop.
  • WHILE: Here we set up the loop to cycle from 0 to 3. Below, we add 1 to the value just before accessing which word in the array we want to make changes to.
  • LOOP: This is where we increase “i_current_array_spot” by 1 each time, so the actual array item being pulled is one higher than 0 to 3; which become 1 to 4.
  • RAISE NOTICE: Return “t_result” to see the following output.

Output

Postgres
MS SQL
Javascript
Python

Note how all spaces, front and rear, are removed from each of our four array items?

Now, let’s study another potential usage of the TRIM() function in PostgreSQL. Before we dive in, let’s have a quickie lesson on…

The CONCAT function

Syntax

t_string_combined = CONCAT(t_string_1, t_string_2, t_string_3);

Note we fed the CONCAT function three parameters to combine. Taking the syntax above and making the example just a bit more real:

t_name_full = CONCAT(t_name_first, " ", t_name_last);

The above SQL script takes the contents of “t_name_first”, adds a space after it, and then adds the contents of “t_name_last” to the end. So, if t_name_first contained “Jim” and t_name_last contained “Johnson” then s_name_full now equals “Jim Johnson”.

But what if we don’t know for sure about whether extra spaces exist at the beginnings or ends of “t_name_first” and “t_name_last”? It’s good practice to anticipate and plan for this. So let’s modify the equation above to include the TRIM() function.

t_name_full = CONCAT(TRIM(t_name_first), " ", TRIM(t_name_last));

In the example above, both “t_name_first” and “t_name_last” are trimmed of excess spaces before we use the CONCAT function to combine them.

Note: A short-hand method for CONCAT is to use the “||” combination of symbols. See:

t_name_full = TRIM(t_name_first) || TRIM(t_name_last);

Let’s now use both the Trim() and Concat() functions in SQL in conjunction with the SELECT, FROM, and WHERE clauses:

SELECT
    CONCAT(TRIM(t_name_first), " ", TRIM(t_name_last))
FROM
    tbl_data
WHERE
    is_user_active = TRUE;

Conclusion

In this tutorial presentation we learned how to use the TRIM() function in our Postgres SQL, including working through some detailed real world examples. We also investigated the use of the CONCAT() function and various PostgreSQL query statements including SELECT, FROM, and WHERE. In addition, we played with the ARRAY type, the ARRAY_LENGTH function, the WHILE loop construct, and the RAISE NOTICE statement.

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.