新建函数名:splitString( )
BEGIN
declare result varchar(255) default '';
set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1));
return result;
END
参数:
f_string varchar(1000),f_delimiter varchar(5),f_order int
举个栗子:
表:emp
需要分割的字段
id
orgname
1
9999.5263.325.21
2
9999.5263.325.22
SELECT
id,
splitString(orgname,'.',1) AS j1,
splitString(orgname,'.',2) AS j2,
splitString(orgname,'.',3) AS j3,
splitString(orgname,'.',4) AS j4
FROM emp;
结果:
id
j1
j2
j3
j4
1
9999
5263
325
21
2
9999
5263
325
22