MySQL: Function to Split String Value Using Different Delimiters and Position

In this post, I am providing one function to split string by using different type of delimiters and position in MySQL.

I have created this function because splitting a string is a very common requirement for all MySQL Database Developer.

I have created this function in such a way that you guys can pass any type of delimiter and split your string, but you also require to pass position to get exact value.

I have also created a similar kind of split function for SQL Server.
You can visit this article here:

For example, we have one column EmployeeName and it contains both Firstname and Lastname which is sperated by space. I require to create two column EmployeeFirstName and EmployeeLastName.

Below is a function and full demonstration of this:

First, Create a user defined function:

1 2 3 4 5 6 7 8 9 10 11

CREATE FUNCTION SplitString_ByAnyDelimiter (   InputString TEXT,   InputDelimiter VARCHAR(8),   InputPosition INT ) RETURNS TEXT RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(InputString, InputDelimiter, InputPosition),        LENGTH(SUBSTRING_INDEX(InputString, InputDelimiter, InputPosition -1)) + 1),        InputDelimiter, '');

Sample Executions:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

SELECT SplitString_ByAnyDelimiter('Anvesh Patel', ' ', 1) AS FirstName   /* Result: FirstName ----------- Anvesh */   SELECT SplitString_ByAnyDelimiter('Anvesh.Patel', '.', 2) AS LastName   /* Result: LastName ----------- Patel */   SELECT SplitString_ByAnyDelimiter('Anvesh|Patel', '|', 1) AS FirstName   /* Result: FirstName ----------- Anvesh */