Trim Function in Postgres SQL
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
TRIMfunction 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
WHERE. We’ll work some with the
ARRAYvariable type, the
RAISE NOTICE, and how to
CASTan integer as text.
- Knowledge of the use of basic SQL statements, including
- Basic awareness of how the following functions work:
- 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
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
-- loop through our four phrases
WHILE i_current_array_spot < i_size_of_array
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;
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.
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
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:
CONCAT(TRIM(t_name_first), " ", TRIM(t_name_last))
is_user_active = TRUE;
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
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