/ ID: 1334 Title: Split_Part in CockroachDB Meta Description: Use of Split_Part in CockroachDB. Code samples included. Meta Keywords: Split_Part CockroachDB Author: Data Pilot Template: Unstructured Tutorial Categories: CockroachDB Tags: Split_Part, CockroachDB Status: Published /

Introduction

In this lesson, we will learn how to use Split_Part in CockroachDB SQL. We'll also explore the Cockroach array data type, Cockroach array_to_string, and the string_to_array functions. In this lesson, we will use the following structure:

• What? What is the description of and syntax for using the CockroachDB Split_Part function and CockroachDB Array data type?
• Why? When and how do we use Cockroach Split_Part and Array in our database SQL?
• More Since Split_Part is an array-related function, there are many ways to utilize it. We'll learn a few of those ways and even practice using a realistic business example. Before we learn about Split_Part, we will explore a closely related Cockroach type called "Array".

Understanding Cockroach Arrays

An array is a list or group of items. In the database programming world, arrays often increase efficiency and save us extra work when dealing with a list of numbers or text. You can call "2, 5, 6, 9, 12" an array of integers or numeric items (and they can be in any order you want; not needing to be consecutive as you see in this example array). You can call "Freddy Kruger, Descending, James Earl Jones, 52, Firefox, Postgres, Function" an array of text items or array of strings. "Items" is the most often used term to refer to each individual in the group that is an array or other kind of list.

In Cockroach' array is data type. So in one cross-section of a row and column you can have - instead of an integer or text data type - an array data type. Here is how you would create a column to be an array:

Cockroach Array syntax

sql CREATE TABLE tblCockroachUsers ( txtEmployee text -- text data type, not array , arrayintGrade integer[] -- this column is the array data type )

In that Cockroach query above, the column "arrayintGrade" may be new for you. The brackets ("[]") are how we tell Cockroach to set the column as an array data type. We named the column with a prefix of "array" to designate "array" as the primary type, then "int" to designate the secondary type as "integer" data type.

Looking at that table filled with some test data, we get a more visual perspective on the concept of how CockroachDB array works in Cockroach databases.

sql SELECT txtEmployee text , arrayintGrade integer[] FROM tblCockroachUsers

Returns the following:

| txtEmployee | arrayintGrade | |-------------|---------------:| | Speve | 50, 75 | | Gill | 98, 90, 95 | | Sheron | 92, 95, 99 | | Tadd | 90, 99, 95, 94 | | Georgo | 90, 95 | | Sul | 80, 97 | | Frad | 84, 82, 89, 88 |

Note: The actual data returned from CockroachDB included "{}" brackets that we removed so you see a less cluttered and easier to understand view of the records returned.

Now to retrieve data from our table and acquire only part of the array; one of the grades; the second one:

sql SELECT txtEmployee text , arrayintGrade integer[2] AS intGradeTwo FROM tblCockroachUsers

Returns the following data:

| txtEmployee | intGradeTwo | |-------------|------------:| | Speve | 75 | | Gill | 90 | | Sheron | 95 | | Tadd | 99 | | Georgo | 95 | | Sul | 97 | | Frad | 82 |

Explanation

The [2] in arrayintGrade integer[2] tells the CockroachDB SQL engine to return the first item in the "arrayintGrade" column and that column is an array. We used CockroachDB's "AS" to give a name to the returned data, which is "intGradeTwo".

Now that you grok the basics of arrays, it's finally time to learn how to use Split_Part() to create a Cockroach array.

Split_Part

Split_part's primary use is to create an array from a text string. The first thing we will do is look at the syntax required for the Split_part function.

Cockroach Split_part syntax

sql SPLIT_PART(txtTextToSplit, txtDelimiter, intPositionToGet)

• txtTextToSplit: This is a Cockroach text string containing a list of items, such as "a, 2, 9" or "Excel, Lotus, Tesla".
• txtDelimiter: This is the comma you most often use to delimit or separate each item within the group of items. If your list is a phrase and you want to acquire each word, you might want to use " " as a delimiter.
• intPositionToGet: Which item (begin counting at 1) in the delineated item group do you want to return? Sort of like "Which word in the sentence do you want to begin with?"

Cockroach Split_part example

sql SPLIT_PART("squirrel, dolphin, rat", ",", 2)

Returns "dolphin" because we used "2" as the intPositionToGet parameter and dolphin is in the 2nd position within the string we supplied to the Cockroach Split_part() function.

Hopefully that made sense to you. Why did we spend so much time learning about ARRAYS first? Because - instead of supplying txtTextToSplit with a literal string, we can supply it with an array, like so:

sql DECLARE arraytxtNames TEXT[] := ARRAY['Franky', 'Banksy', 'Stanky']; txtNames TEXT := array_to_string(arraytxtNames, ","); BEGIN return split_part(txtNames, ",", 1); END

Analysis:

• arraytxtNames: Here we have declared (initialized) our Cockroach array and filled it with three peoples' names.
• txtNames: This is using the Cockroach array_to_string function to transform our array into a string that looks like "Franky, Banksy, Stanky".
• split_part: Here we are asking that the 1st item in the list be returned, which happens to be "Franky".

Now that we understand how the CockroachDB Split_Part function and arrays work, let us take a quick look at a related function, string_to_array.

Cockroach String_to_array

Below we will look at the syntax and an example of the CockroachDB string_to_array function.

Cockroach String_to_array syntax

``` sql STRING_TO_ARRAY(txtTextToSplit, txtDelimiter, [optional null handling here]) ````

• txtTextToSplit: This is a list of items, which might look like either "4, 1, 8" or "Table, Record, Row, Cockroach".
• txtDelimiter: This is the delimeter (like comma) you used to separate each individual item within the string of items. If your list/array is a phrase and you want to bring each word into your array, you would use " " as a delimiter. Note: Keep in mind the phrase might have commas or other special characters in addition to spaces to separate words.
• optional null handling here: Optional string to handle a potential null parameter sent to the Cockroach function.

Cockroach String_to_array example

sql arraytxtTechnology = STRING_TO_ARRAY("Table,Record,Row,Cockroach", ",")

Analysis: creates and fills the arraytxtTechnology Cockroach array to look like:

{"Table", "Record", "Row", "Cockroach"}

Miscellaneous

If you are wondering why we named some columns and variables with a prefix of "arraytxt", "int", or "txt": we call this "using naming conventions" or "being smart with your coding" to future-proof your code in terms of ease of readability, especially when someone else inherits your code. This practice is almost guaranteed to increase your efficiency as a programmer.

Conclusion

In this lesson, we learned how and why to use the Cockroach Split_Part function, the Cockroach Array_to_String function, the Cockroach String_to_Array function, and the ARRAY data type in Cockroach SQL. We also used the DECLARE statement. There is much more we can do with arrays and lists. Look for future documents that show many other ways to use these powerful data types and functions available to you in CockroachDB. Code samples included.