Concat in CockroachDB

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In this document, we learn to use Concat in CockroachDB when combining strings in SQL, including:

  • What? What does the Cockroach CONCAT function do?
  • How? How do we use the Concat function in our CockroachDB 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 will learn about an ease-making short-hand method of concatenation.

What is Cockroach CONCAT?

The CockroachDB CONCAT function combines two or more strings. This process is called concatenation. There is a shortcut/shorthand way of doing the same process that we will look at later.

Concat syntax

How to use this function with SQL and parameters:

1
txtStringCombined := CONCAT(txtString1, txtString2, txtString3);

Notice, we fed the CONCAT function three parameters to concatenate. Taking the syntax above and making the example just a bit more realistic:

1
txtNameFull := CONCAT(txtNameFirst, " ", txtNameLast);

The above SQL script takes the contents of “txtNameFirst”, adds a space after it, and then adds the contents of “txtNameLast” to the end. So, if txtNameFirst contained “Tad” and txtNameLast contained “Biffson” then txtNameFull now has a value of “Tad Biffson”.

Now we will try out using the Cockroach Concat function in more of a database context in conjunction with the SELECT statement:

1
2
3
4
5
6
SELECT
    CONCAT(txtColumnData, "-NEW")
FROM
    tblData
WHERE
    dateSampled > '2020-03-18';

The example above serves to return the “txtColumnData” column contents with “-NEW” tacked on to the end and this is with only rows where the date in the “dateSampled” column are later than March 18th, 2020.

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

1
2
3
4
5
6
7
8
9
SELECT
    txtColumnData
    , UPPER(LEFT(txtColumnData,1)) AS txtLetterFirst
    , RIGHT(txtColumnData, LENGTH(txtColumnData)-1) AS txtPhraseEnd
    , CONCAT(txtLetterFirst, txtPhraseEnd) AS txtPhraseNew
FROM
    tblData
WHERE
    dateSampled > '2020-03-18';

Analysis of the above SQL, line by line:

  • SELECT txtColumnData: We don’t necessarily need this field, but are going to display it for purposes of increased lesson clarity.
  • SELECT UPPER(LEFT(txtColumnData,1)): In this line of our SQL code we grab the first character in our “txtColumnData” column and change it to uppercase. Finally, the “AS” statement temporarily stores this character in a new column named “txtLetterFirst”.
  • SELECT RIGHT(txtColumnData, LENGTH(txtColumnData)-1): In this part we use the Cockroach 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 text without its first character. And that first character will be provided in the next line of code.
  • SELECT CONCAT(txtLetterFirst, txtPhraseEnd): Here we use the CONCAT function to combine “txtLetterFirst” with “txtPhraseEnd”.
  • FROM: Tells Cockroach which table we are getting our data from; “tblData”.
  • WHERE: Filters the rows of the returned record set by whether the contents of the “dateSampled” column are more recent than March 18, 2020.

CONCAT function example

Now we’ll take a look at another example in SQL of how CONCAT can help. In our mini-project, we have a table called “tblCompanyTech”. We want to return text from the txtCategory column, filter by a certain phrase, remove that phrase, and concatenate a word to the end. We’ll pause to look at a representation of the table structure and data:

txtNametxtCategory
PostgreSQLDatabase Relational Monolithic
MongoDatabase NoSQL Flexible
CockroachDBDatabase Relational Free Open Source
JavascriptLanguage Easy Pervasive
PythonLanguage Newish Flexible
PHPLanguage Old Pervasive Open Source
JavaLanguage Powerful Pervasive

Our goal for this project is to return the contents of the txtCategory column, but replacing the last word if that string is “Open Source”. First we will write up a plan.

  • (a) Acquire the data as a string of text.
  • (b) Check to see if “Open Source” is at the end of that text.
  • (c) Get the length of that text column data.
  • (d) Return the left side of that data minus the length of ” Open Source”.
  • (e) Add ” Free” to the end of the new string.
1
2
3
4
5
6
7
SELECT
    LEFT(txtCategory,CHAR_LENGTH(txtCategory) - CHAR_LENGTH(" Open Source")) AS txtCatMinusWords
    , CONCAT(txtCatMinusWords, " Free") AS txtCategory_new
FROM
    tblCompanyTech
WHERE
    RIGHT(txtCategory,12) = ' Open Source'

Analysis

  • SELECT LEFT: Beginning with text pulled from the “txtCategory” 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 “txtCategory” using the CHAR_LENGTH function. Our formula takes the full length of the data supplied to us and reduces that length by the length of the words we are removing, which comes out to twelve characters. Finally, we place the resulting value into “txtCatMinusWords” with the “AS” operator.
  • SELECT CONCAT: Here we concatenate ” Free” to the end of the value “txtCatMinusWords” that we created above.
  • FROM: This tells Cockroach to acquire our data from the table called “t_tblCompanyTech”.
  • WHERE RIGHT: Here we are telling CockroachDB to only look at rows WHERE the last 12 characters in the “txtCategory” field equal ” Open Source”.

Short hand for CONCAT

1
txtStringCombined := CONCAT(txtString1, txtString2, txtString3);

Can also be expressed more efficiently as

1
txtStringCombined := txtString1 || txtString2 || txtString3;

OR

1
txtNameFull := CONCAT(txtNameFirst, " ", txtNameLast);

Can be rewritten in shorthand as

1
txtNameFull := txtNameFirst || " " || txtNameLast;

Miscellaneous

In many programming languages, like with arrays, for example, we begin 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.

Conclusion

In this document we explored how to use the CONCAT function in Cockroach SQL. We also learned via a realistic business example to execute Cockroach Concat. Finally, we explored the use of LEFT, RIGHT, CHAR_LENGTH, and UPPER in CockroachDB. We also learned of a short hand method for concatenation using the “||” symbols, for more rapid development. Code samples included.

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.