Trick to split text in select statement

hello every body,,

in this post i will share and explain a function that split text by (delimiter string), using MySQL to select the index array the result will be shown in a field ,,,,

code

delimiter //
DROP FUNCTION IF EXISTS split_str_1;
CREATE FUNCTION split_str_1(_strtxt text,_strdelimiter tinytext,_numindex int) returns text
begin
return substring_index(substring_index(_strtxt,_strdelimiter,_numindex),_strdelimiter,-1);
end
//

the function format is :

split_str_1([string | field-name | Variable], [Delimiter value], [index])

here is an example how to use it:

i have table contain a one row, so we want to split that string by “,” delimiter ..

+++++++++++++++++++
+       F2        +
+++++++++++++++++++
+  "a1,a2,a3,a4"  +
+++++++++++++++++++

Ex. :-

SELECT split_str_1(F2, ',' , 1) AS 'index1' FROM tab1;
# Result
+++++++++++++++++++
+       index1    +
+++++++++++++++++++
+      "a1"       +
+++++++++++++++++++


select split_str_1(F2, ',' , 2) as 'index2' from tab1;
# Result
+++++++++++++++++++
+     index2      +
+++++++++++++++++++
+      "a2"       +
+++++++++++++++++++



select split_str_1(F2, ',' , 3) as 'index3' from tab1;
# Result
+++++++++++++++++++
+     index3      +
+++++++++++++++++++
+       "a3"      +
+++++++++++++++++++


select split_str_1(F2, ',' , 4) as 'index4' from tab1;
# Result
+++++++++++++++++++
+     index4      +
+++++++++++++++++++
+      "a4"       +
+++++++++++++++++++


select split_str_1(F2, ',' , 5) as 'index5' from tab1;
# Result
+++++++++++++++++++
+      index5     +
+++++++++++++++++++
+       "a4"      +
+++++++++++++++++++

there you can note that function returned the last array value if you use the last index or above, also the index number start from 1.

some time you get warning message for safe and trust function,, so you need add this line above of code:

SET GLOBAL log_bin_trust_function_creators = 1;

I wish that I Introduced a useful subject,

best regard