Rajendra Gupta
Multiple string delimiters

Python scripts to split and concatenate strings

April 23, 2020 by

This article gives an overview of Python Script functions to split strings and string concatenation functions.

Introduction

Python is a versatile language. It contains many useful functions, libraries, modules that provide you with the flexibility to write your code. SQL Server 2017 onwards we can execute Python codes inside SQL Server. You need to install Machine learning services for using Python in SQL Server.

I would recommend you to go through the following lines before proceeding with this article.

In this article, we explore Python useful function to SPLIT and do string concatenation using Python Scripts.

Prerequisites

  • SQL Notebooks in the Azure Data Studio: I use the Azure Data Studio march release in this article to create a SQL Notebook and execute code. We can run SQL, Python, Spark, PowerShell codes in the notebook. You can use separate tools as well for it, but I would recommend using it for both T-SQL and Python
  • PIP Python module: Validate the PIP module in Python. Click on Manage in the SQL notebook of Azure Data Studio and verify it

SQL Notebooks in the Azure Data Studio

Python Script function – SPLIT

In Python, we do not have a character data type. It uses Unicode characters for the string. It also considers a single character as a string. Sometimes, we need to split a string based on the separator defined. It is similar to a text to columns feature in Microsoft Excel.

SPLIT function without any arguments

Look at the following code. Here, we use the SPLIT function without any separator.

It breaks the string into smaller chunks. By default, it considers space as a string separator. In the above query, we get split strings on each occurrence of white space.

Python Script function

Now, we make a slight change in the Python Script. It contains special characters (comma and separator ).

  • For string (Hi,), it considers it a single word because it does not contain white space. It does not split it
  • For string (- SPLIT), we have space after separator, so It considers it as a separate string and splits it

String function output

If we remove space from between two words (for example Hi, You in below example), it does not split them.

Here, in the output, we can note the difference.

Examples

SPLIT function arguments

We can use the following parameters in the SPLIT function.

  • Separator: In the Excel’s text to column functionality, we define a separator such as a comma, semicolon to split a string. Similarly, we use a separator in Python to split it on the occurrence of separator

    In the following script, we use a comma separator. Once we execute this script, it splits the string on the occurrence of a comma separator.

    In the case of multiple separators, the string gets splits on each occurrence of the separator, as shown below:

    Output:

    SPLIT function arguments

  • Max Number of splits

    We can specify a maximum number of splits in a string. It is an optional parameter. By default, Python function split the complete string based on the separator defined. It splits on each occurrence of the separator

    In the below script, we have multiple separators. Suppose we need a certain number of string splits using Python scripts. In this case. We use maximum splits of the string parameter. Therefore, we specify value 4 in the SPLIT function to tell that it should stop splitting string after 4 splits

    We can verify splits in the following output:

    Max Number of splits

    We can use a string as a delimiter as well in Python Scripts. In the following code, we want to split a string based on ‘And’ separator

    The output shows a split string based on string delimiter and a maximum number of split values

    Multiple string delimiters

Python String Concatenation

In the previous section, we split the string based on a delimiter and a maximum number of split parameters. Sometimes we also require to join the strings together. For example, suppose we have two datasets containing first and last name of the customer. We want to concatenate string together.

We have several ways to concatenate strings in Python scripts. Let’s look at few such ways.

Concatenate using the plus(+) operator:

It is a common way of concatenate in most languages. We use a plus operator to concentrate two or more strings using this way. We use the plus operator in SQL Server as well however we have many functions available for it as well.

In the above example, we concatenate three strings together using the plus operator and the output is a concatenated string as shown below.

Python String Concatenation

We can add white space or any special characters in the string using a similar plus operator. For example, in the below code, we specify space and separator in a string.

Here, we get the output with a white space between the first and second words:

Examples of Python String Concatenation

Use JOIN function for string concatenation

Python uses a JOIN function to concatenate multiple strings together. In this function, we can specify separator as well.

In this example, instead of specifying the strings directly, we ask for user input for entering a string. In the print statement, we specify white space in the double quote and use the JOIN function to concatenate strings.

Once you execute this code, it asks for the first user input.

Use JOIN function for string concatenation

Write the string in the box and press Enter. It asks for the second string.

Input box

Press Enter, and it returns the concatenated string.

Concatenated string

Use FORMAT function for string concatenation

We can also use FORMAT function in Python scripts to perform string concatenations. We can also use separators or delimiters in this function.

In the following query, we use white space between two Brackets and specify strings in the format function.

In the output, we get a white space between as a result of string concatenation. Let’s specify a special character in the format function between the brackets.

In the output, you get the character between the strings.

Use FORMAT function for string concatenation

Use f strings for string concatenation

We can use f strings ( in Python 3.6+) for string concatenation as well. It is also known as Literal String Interpolation. In the F-strings, we use the embed expressions inside string literals. We use a prefix f in the code.

In the following code, we specify string concatenate using the f strings.

We get the following concatenated string output using f strings function.

Use f strings for string concatenation

Conclusion

In this article, we explored Python functions to split and concatenate strings in a Python script. You should be familiar with the available function to use it appropriately. You can use them in Python SQL scripts as well to run Python code from SQL Server. We will cover more useful functions and methods in upcoming articles.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)

Related posts:

  1. Use Python SQL scripts in SQL Notebooks of Azure Data Studio
  2. SQL Server PRINT and SQL Server RAISERROR statements
  3. SQL Server CONCATENATE Operations with SQL Plus (+) and SQL CONCAT functions
  4. Using PowerShell to split a string into an array
  5. Setting up Visual Studio Code for Python Development
168 Views