Postgres Concat Function in SQL

Introduction

In this article, we learn to use the Postgres Concat function when combining strings in SQL, including:

  • What? What does the CONCAT function do?
  • How? How do we use the Concat() function in our PostgreSQL query execution? What kind of parameters do we give it?
  • Why? When do we need this function?
  • Extras We’ll also get exposure to the use of other functions such as LEFT, RIGHT, CHAR_LENGTH, and UPPER. Finally, we’ll learn about a short hand method of concatenation.

Prerequisites

  • Understanding of how to write 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 and FROM.
  • Understanding of what the following terms mean: text types, strings, and concatenation.

What is the CONCAT function in Postgres?

The PostgreSQL CONCAT function combines two or more strings.

How to use this function with SQL and parameters

Syntax

t_string_combined = CONCAT(string1, string2, string3);

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

Let’s now try using the Concat() function in more of a database context in conjunction with the SELECT clause:

SELECT
    CONCAT(t_field_data, "-NEW")
FROM
    tbl_data
WHERE
    d_sampled > '2018-01-01';

The example above serves to return the “t_field_data” column contents with “-NEW” tacked on to the end and this is with only rows where the date in the “d_sampled” column are later than January 1st, 2018.

What if we want to capitalize the first letter of a phrase?

SELECT
    t_field_data
    , UPPER(LEFT(t_field_data,1)) AS t_letter_first
    , RIGHT(t_field_data, LENGTH(t_field_data)-1) AS t_phrase_end
    , CONCAT(t_letter_first, t_phrase_end) AS t_phrase_new
FROM
    tbl_data
WHERE
    d_sampled > '2018-01-01';

Analysis of the above SQL, line by line:

  • SELECT t_field_data: We don’t necessarily need this field, but are going to display it for purposes of increased lesson clarity.
  • SELECT UPPER(LEFT(t_field_data,1)): In this line of our SQL code we grab the first character in our “t_field_data” column and change it to uppercase. Finally, the “AS” statement temporarily stores this character in a new column named “t_letter_first”.
  • SELECT RIGHT(t_field_data, LENGTH(t_field_data)-1): In this part we use the LENGTH function to determine the overall length of our string, subtract 1, and use that number to count backwards from the end of our source string, which yields source string without its first character. And that first character will be provided in the next line of code.
  • SELECT CONCAT(t_letter_first, t_phrase_end): Here we use the CONCAT() function to combine “t_letter_first” with “t_phrase_end”.
  • FROM: Telling Postgres which table we are getting our data from; “tbl_data”.
  • WHERE: Filtering the rows of the returned data set by whether the contents of the “d_sampled” column are more recent than January 1, 2018.

Real world use of the CONCAT() function

Let’s look at another example in SQL of how CONCAT works. In our mini-project, we have a table called “company_tech”. We want to return text from the t_category column, filter by a certain phrase, remove that phrase, and add (concatenate) a word to the end. We’ll pause to look at a representation of the table structure and data:

t_namet_category
OracleDatabase Relational Monolithic
MongoDatabase NoSQL Flexible
PostgreSQLDatabase Relational Free Open Source
MySQLDatabase Relational Free Open Source
PythonLanguage Newish Flexible
PHPLanguage Old Pervasive Open Source
JavaLanguage Powerful Pervasive
JavascriptLanguage Easy Pervasive

Our goal is to return the contents of the tcategory field, but replacing the with the last word _if that phrase is “Open Source”. First we’ll make a plan.

  • (1) Acquire the data as a string.
  • (2) Is “Open Source” at the end of that string?
  • (3) Get the length of that data.
  • (4) Return the left side of that data minus the length of ” Open Source”.
  • (5) Add ” Free” to the end.
SELECT
    LEFT(t_category,CHAR_LENGTH(t_category) - CHAR_LENGTH(" Open Source")) AS t_cat_minus_word
    , CONCAT(t_cat_minus_word, " Free") AS t_category_new
FROM
    company_tech
WHERE
    RIGHT(t_category,12) = ' Open Source'

Analysis:

  • SELECT LEFT: Beginning with text pulled from the “t_category” field (column) in our database, we know this string has ” Open Source” at the end because of the filtering of the WHERE clause below. So we need to cut off those last 12 characters, which means we want the left side of our string. But every row is going to provide a different length string, so we need to calculate overall length of “t_category” using the CHAR_LENGTH function. Our formula takes the full length of the data given us and reduces that length by the length of the word we are removing, which comes out to twelve characters. Finally, we place the resulting value into AS “t_cat_minus_word”.
  • SELECT CONCAT: Here we concatenate ” Free” to the end of the value “t_cat_minus_word” that we created above.
  • FROM: This tells Postgres to acquire our data from the table called “t_company_tech”.
  • WHERE RIGHT: Here we are telling PostgreSQL to only look at rows WHERE the last 12 characters in the “t_category” field equal ” Open Source”.

Miscellany

  • In many situations, like arrays, for example, we start counting at zero. So it is important to keep in mind that with LEFT() and RIGHT() we start counting positions at the value of one, not zero.
  • Did you wonder why we named some variables and columns 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.

There is a “short hand” way of doing concatenation

t_string_combined = CONCAT(string1, string2, string3, etc);

Can also be expressed as

t_string_combined = string1 || string2 || string3 || etc;

OR

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

Can be changed to

t_name_full = t_name_first || " " || t_name_last;

Conclusion

In this article we explored how to use the CONCAT() function in Postgres SQL. We also learned via a real world example. Finally, we also explored the use of LEFT, RIGHT, CHAR_LENGTH, and UPPER. We also learned of a short hand method of concatenation using the “||” symbols.

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.