This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing one user defined function to select a particular value from comma separated string in the MySQL.
In our MySQL Database, we are managing some of the table in which we are storing dynamic content management system (CMS) related data.
As per the requirement, we are also storing comma delimited values in some the tables.
Now the requirement is to find third value from each comma separated string.
I have created one user defined function to solve this problem.
First, Create UDF to split and select a particular value from comma separated string:
Three parameters require: Delimited_String, Delimited_Character, and Position.
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE FUNCTION fn_Get_Split_String ( DelimitedString VARCHAR(255) ,DelimitedCharacter VARCHAR(12) ,Position INT )RETURNS VARCHAR(255) RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(DelimitedString, DelimitedCharacter, Position) ,LENGTH(SUBSTRING_INDEX(DelimitedString, DelimitedCharacter, Position-1)) + 1) ,DelimitedCharacter, ''); |
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE tbl_CommaSeparated ( ID INTEGER ,CommaDelimited TEXT ); INSERT INTO tbl_CommaSeparated VALUES (1,'A,B,C,D,E,F') ,(2,'12,45,89,36,88') ,(3,'a,b,c,d,e,f'); |
Using UDF, SELECT second value from each comma separated string:
|
1 2 |
SELECT fn_Get_Split_String(CommaDelimited,',',2) AS ResultData FROM tbl_CommaSeparated; |
The Result:
