简介

这是最近工作中遇到的一个业务场景，需要按字符将字符串拆分成多个子字符串，这就类似编程语言中的split函数。

详解

业务场景

需要从表中查询一个字段，这个字段是由多个或者一个字符串拼接而成，中间由,隔开。
所以需要拆分这个字段，然后依次用拆分后字符串做为条件从另一个表中查询一个字段，最后再用,拼接查询结果。
看一下示例：

select aa from table_a;
--先假设查询结果aa的一个为 111,222
select bb from table_b where bb2='111';
--假设查询结果bb为1212 
select bb from table_b where bb2='222';
--假设查询结果bb为1313

而我最终要获取到得结果是 1212,1313

SQL server实现

SQL server中有一个分割字符串的函数parsename，直接看实例如何使用。

select parsename('11.22',1);
select parsename('11.22',2);
select parsename('11.22',3);
select parsename('11,22',3);

--查询结果依次是
22
11
null
11,22

这个函数会根据.拆分字符串，且会根据第二个参数返回指定的子字符串，1则为倒数第一个，2则为倒数第二个。
有了这个函数就可以像split函数一样获取到每个子字符串。再配合replace函数可以实现指定字符拆分。看实例：

select parsename(replace('11,22',',','.'),1);
select parsename(replace('11,22',',','.'),2);

--查询结果依次是
22
11

通过创建函数来实现业务场景功能

CREATE FUNCTION dbo.fn_split_str(@in varchar(128))
RETURNS VARCHAR(128)
AS
BEGIN
  DECLARE @out varchar(128)
  DECLARE @num INT
 SET @out = ''
 SET @num = 1
 set @in=replace(@in,',','.')
 WHILE PARSENAME(@in, @num) IS NOT NULL
 BEGIN
  SELECT @out=@out+column_name+',' FROM table_name WHERE code=PARSENAME(@in, @num)
    set @num=@num+1
 END
  SET @out=SUBSTRING(@out,1,LEN(@out)-1)
 RETURN @out
END

注意替换table_name和column_name为自己的表名和列名。

MySQL实现

主要使用SUBSTRING_INDEX函数实现，直接看实例如何使用。

SELECT SUBSTRING_INDEX('11.22.33','.',1) UNION ALL
SELECT SUBSTRING_INDEX('11.22.33','.',2) UNION ALL
SELECT SUBSTRING_INDEX('11.22.33','.',3) UNION ALL
SELECT SUBSTRING_INDEX('11.22.33','.',4) UNION ALL
SELECT SUBSTRING_INDEX('11.22.33','.',-1);

--查询结果依次是
11
11.22
11.22.33
11.22.33
33

与SQL server的PARSENAME有些差别，一可以指定分割的字符，二是正序返回结果，三是第三个参数代表返回前几个拆分的子字符串而不是第几个。

创建函数实现业务场景，配合SUBSTR截取字符串函数和INSTR返回字符在字符串的位置函数实现

DELIMITER $$
CREATE FUNCTION fn_split_str (input VARCHAR(128)) 
RETURNS VARCHAR(128)
DETERMINISTIC
NO SQL
BEGIN
    DECLARE output VARCHAR(128);
    SET output = '';
    WHILE SUBSTRING_INDEX(input,',',1)!=0 DO
        SELECT CONCAT(output, column_name, ',') INTO output FROM table_name WHERE mid = SUBSTRING_INDEX(input,',',1);
        IF INSTR(input,',')!=0 THEN
            SET input=SUBSTR(input,INSTR(input,',')+1);
    ELSE
            SET input='';
        END IF;
    END WHILE;
    RETURN TRIM(TRAILING ',' FROM output);
END $$
DELIMITER ;

注意替换table_name和column_name为自己的表名和列名。

期间遇到了如下报错信息：