MySQL Split

What is MySQL? What are the features of MySQL?

MySQL is the most widely used Open-Source SQL database management system. MySQL is provided support by Oracle Corporation.

MySQL is a database management system.

• A collection of structured data is called a database, ranging from simple lists to corporate networks. To access, process, and add data, a database management system like MySQL Server is required.
• Databases are essential in computing, serving as standalone utilities or part of other applications due to their ability to handle large amounts of data.

MySQL Database is Relational.

• A relational database stores data in separate tables, offering a flexible programming environment. It enforces rules governing data relationships to prevent inconsistent, duplicate, orphaned, out-of-date, or missing data, using SQL as the most common standardized language.

MySQL software is open-source.

• Open-source software like MySQL is free to use and modify, while commercially licensed versions can be purchased for commercial applications or if the GPL is uncomfortable.

A large amount of contributed MySQL software is available.

• MySQL Server is a user-friendly application that is compatible with your laptop or desktop.

MySQL HeatWave.

• MySQL HeatWave is a managed database service that integrates transactions, real-time analytics, and machine learning into one database, eliminating complexity, latency, risks, and costs associated with ETL duplication.

What is a string?

In MySQL, strings are data types used to store alphanumeric or textual data, including letters, numbers, symbols, and binary data. There are various string data types available with unique functions, allowing for efficient data storage. In MySQL, a few popular string data types are as follows:

• CHAR: This data type is a fixed-length string type.
• VARCHAR: This data type is a variable-length string type.
• TEXT: This data type is used for longer text, like documents or big paragraphs.
• BLOB: BLOB stands for Binary Large Object. This data type is used for binary data type.
• ENUM: This data type is used to specify possible values for a column.
• SET: This data type is also similar to the ENUM data type, but it can store multiple values.

What is String Splitting?

String splitting is the logical division of a text string into its component pieces so that they can be treated separately. For the purpose of the numerical analysis, a timestamp might, for instance, be divided into hours, minutes, and seconds.

The reasons for String Splitting

• String splitting is a useful tool for parsing and analyzing data in delimited formats like CSV or tab-separated values.
• It allows for the extraction of relevant data elements, normalizing data by splitting a combined field into separate columns, aggregating data based on specific criteria within a string, cleaning data by removing unwanted characters or substrings and transforming data from one format to another.
• It can also be used for search and filtering, enabling the search for records containing specific substrings within a text field by splitting the text and performing searches on individual components. Overall, string splitting is a versatile tool for data organization and analysis.

Introduction to MySQL String function

MySQL has built-in functions for various operations with string data, including splitting data based on delimiters during SELECT queries. The SUBSTRING_INDEX () function splits the retrieved string data based on a specific delimiter.

Syntax of SUBSTRING_INDEX ():

This function takes three arguments. Following is the syntax of SUBSTRING_INDEX () functions:

string SUBSTRING_INDEX(string, delimiter, count);

• The split function uses a string as the first parameter, a delimiter to divide the string, and a count to indicate the delimiter's number of occurrences, with positive values returning the substring value from the left and negative values returning it from the right.

Using the SUBSTRING_INDEX () Function, split a string:

Example 1: Separate Text Using the Positive Count Value

• Run the following select statement, which utilizes the SUBSTRING_INDEX () function with a delimiter of space and a positive count value of 1. The main string is "Welcome to JavaTpoint." Thus, the Output will contain the first word in the string.

Output:

Welcome

• Run the following select statement with the positive count value 2 and the o as the delimiter.

Output:

 
Welcome t

• Run the following select statement with the positive count value 1 and the to as the delimiter.

Output:

Welcome 

• Run the following select statement with the positive count value 3 and the to as the delimiter.

Output

 
Welcome to JavaTpoint

Example 2: Separate Text Using the Negative Count Value

• Run the following select statement with a delimiter of space and a negative count value of -1. The main string is "Welcome to JavaTpoint." Thus, the Output will contain the first word in the string.

Output:

JavaTpoint

• Run the following select statement with the negative count value -2 and the e as the delimiter.

Output:

Lcome to JavaTpoint

• Run the following select statement with the negative count value -2 and the a as the delimiter.

Output:

JvTpoint

Example 3: In the table, split the string

• Run the following query to create a database named Test_Database.

• Run the following query statement

• Run the following query.

• Add value to each column in the student table.

• Run the below query to read all records from the student table.

Output:

• Run the query

Output