Postgres Concat Function in SQL
In this article, we learn to use the Postgres Concat function when combining strings in SQL, including:
- What? What does the
- 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
UPPER. Finally, we’ll learn about a short hand method of concatenation.
- Knowledge of the use of basic SQL (PL/SQL) statements, including
- Understanding of what the following terms mean: text types, strings, and concatenation.
What is the CONCAT function in Postgres?
CONCAT function combines two or more strings.
How to use this function with SQL and parameters
Notice, we fed the CONCAT function three parameters to combine.
Taking the syntax above and making the example just a bit more real:
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:
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?
, 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
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:
|Oracle||Database Relational Monolithic|
|Mongo||Database NoSQL Flexible|
|PostgreSQL||Database Relational Free Open Source|
|MySQL||Database Relational Free Open Source|
|Python||Language Newish Flexible|
|PHP||Language Old Pervasive Open Source|
|Java||Language Powerful 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.
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
RIGHT(t_category,12) = ' Open Source'
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”.
- 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
Can also be expressed as
Can be changed to
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
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