Split function in mysql

There is no split function in mysql. You need to write it yourself: 1) obtain the number of strings separated by specified characters: SQL code DELIMITER $ DROPFUNCTIONIFEXISTS 'sims '. 'func _ get_split_string_total '$ CREATEDEFINER 'root' @ 'localhost' FUNCTION 'func _ get_split_string_total' (f_strin

There is no split FUNCTION in mysql. You need to write it yourself: 1) obtain the number of strings separated by specified characters: SQL code DELIMITER $ DROP FUNCTION IFEXISTS 'sims '. 'func _ get_split_string_total '$ create definer = 'root' @ 'localhost' FUNCTION 'func _ get_split_string_total' (f_strin

There is no split function in mysql and you need to write it yourself:

1) obtain the number of strings separated by specified characters:

SQL code

1. DELIMITER $
3. Drop function if exists 'sims'. 'func _ get_split_string_total '$
5. Create definer = 'root' @ 'localhost' FUNCTION 'func _ get_split_string_total '(
6. F_string varchar (1000), f_delimiter varchar (5)
7. ) RETURNS int (11)
8. BEGIN
9. Declare returnInt int (11 );
10. If length (f_delimiter) = 2 then
11. Return 1 + (length (f_string)-length (replace (f_string, f_delimiter, '')/2;
12. Else
13. Return 1 + (length (f_string)-length (replace (f_string, f_delimiter ,'')));
14. End if;
15. END $
17. DELIMITER;

For example, func_get_split_string_total ('abc | def | gh', '|') returns 3.

2) returns the string after the I-th segment.

SQL code

1. DELIMITER $
3. Drop function if exists 'sims'. 'func _ get_split_string '$
5. Create definer = 'root' @ 'localhost' FUNCTION 'func _ get_split_string '(
6. F_string varchar (1000), f_delimiter varchar (5), f_order int) RETURNS varchar (255) CHARSET utf8
7. BEGIN
8. Declare result varchar (255) default '';
9. Set result = reverse (substring_index (f_string, f_delimiter, f_order), f_delimiter, 1 ));
10. Return result;
11. END $
13. DELIMITER;

For example: func_get_split_string ('abc | def | gh', '|', 2) is def

3) Requirement: A field value in Table a is 1 | 2. In the select statement, A and B must be obtained through association with the B dictionary table.

SQL code

1. Create definer = 'root' @ 'localhost' FUNCTION 'getdictname' (v_str varchar (100) RETURNS varchar (100) CHARSET utf8
2. BEGIN
4. DECLARE I int (4 );
5. DECLARE dictCode varchar (100 );
6. DECLARE dictName varchar (100 );
7. DECLARE returnStr varchar (100 );
9. Set I = 1;
10. Set returnStr = '';
12. If (v_str is null or length (v_str) = 0) then
13. Return returnStr;
14. Else
16. While I <= func_get_split_string_total (v_str, '| ')
17. Do
18. Set dictCode = func_get_split_string (v_str, '|', I );
20. Select names into dictName from sims_dd_dict where code = dictCode;
22. Set returnStr = concat (returnStr, ',', dictName); -- use a comma (,) in Chinese. Otherwise, the data is serialized during EXCEL export because the program is separated by commas (,).
23. Set I = I + 1;
24. End while;
26. Set returnStr = subString (returnStr, 2 );
27. Return returnStr;
29. End if;
30. END $