This article is half-done without your Comment! *** Please share your thoughts via Comment ***
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 */ |
