
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
How can we split the name string into three parts by using MySQL SUBSTRING_INDEX() function?
To make it understand, we are using the following data from a table named ‘customerdetail’.
mysql> Select * from Customerdetail; +----------------------+----------------------+----------+---------------------+ | Name | FName | Address | Emailid | +----------------------+----------------------+----------+---------------------+ | Advik Jhamb | Lovkesh Jhamb | Mumbai | Advik@gmail.com | | Chirag Jai Patil | Raman Jai Patil | Gujrat | chirahp@yahoo.com | | Devansh Singh Rajput | Kishore Singh Rajput | Rajastan | Devansh@Hotmail.com | | Mitul Kumar Sharma | Om Veer Sharma | Patiala | Mitul@gmail.com | +----------------------+----------------------+----------+---------------------+ 4 rows in set (0.00 sec)
Now, suppose if we want to split the name into three parts, ‘First_name’, Middle_Name and ‘Last_name’ then it can be done with the help of the following query −
mysql> SELECT Name, SUBSTRING_INDEX(SUBSTRING_INDEX(Name, ' ', 1), ' ', -1) AS First_Name, If( length(Name) - length(replace(Name, ' ', ''))>1, SUBSTRING_INDEX(SUBSTRING_INDEX(Name, ' ', 2), ' ', -1) ,NULL)AS Middle_Name, SUBSTRING_INDEX(SUBSTRING_INDEX(Name, ' ', 3), ' ', -1) AS Last_Name, Address FROM customerdetail; +----------------------+------------+-------------+-----------+----------+ | Name | First_Name | Middle_Name | Last_Name | Address | +----------------------+------------+-------------+-----------+----------+ | Advik Jhamb | Advik | NULL | Jhamb | Mumbai | | Chirag Jai Patil | Chirag | Jai | Patil | Gujrat | | Devansh Singh Rajput | Devansh | Singh | Rajput | Rajastan | | Mitul Kumar Sharma | Mitul | Kumar | Sharma | Patiala | +----------------------+------------+-------------+-----------+----------+ 4 rows in set (0.00 sec)
From the result set of above query, it is clear that the name has been divided into three parts. It considers the middle name as NULL if the name does not consist middle name.
- Related Articles
- How can we split the name string into two parts by using MySQL SUBSTRING_INDEX() function?
- How can we split an IP Address into four respective octets by using MySQL SUBSTRING_INDEX() function?
- Split string into equal parts JavaScript
- How can we upload data into MySQL tables by using mysqlimport?
- How can we extract a substring from a string in MySQL?
- How can we upload data into multiple MySQL tables by using mysqlimport?
- How can we import CSV files into MySQL tables by using mysqlimport?
- How can we replace all the occurrences of a substring with another substring within a string in MySQL?
- How we can split Python class into multiple files?
- How can we check that by default MySQL CHAR() function returns a binary string?
- Can we use reserved word ‘index’ as MySQL column name?
- How can we split a string by sentence as a delimiter in Java?
- In MySQL, how can we insert a substring at the specified position in a string?
- How can we set up a MySQL User account by using INSERT INTO statement?
- How can we insert data into an existing MySQL table by using PHP script?

Advertisements