SQL字符串分割——多种角度详细阐述

一、SQL字符串分割函数

在SQL中，字符串分割最基本的方法就是使用内置的字符串分割函数。不同的数据库都有不同的字符串分割函数，下面以SQL Server和MySQL为例：

-- SQL Server字符串分割函数
SELECT * FROM STRING_SPLIT('apple,banana,orange', ',')

-- MySQL字符串分割函数
SELECT SUBSTRING_INDEX('apple,banana,orange', ',', 1) AS fruit1,
       SUBSTRING_INDEX(SUBSTRING_INDEX('apple,banana,orange', ',', 2), ',', -1) AS fruit2,
       SUBSTRING_INDEX('apple,banana,orange', ',', -1) AS fruit3;

以上代码分别演示了SQL Server和MySQL的字符串分割函数使用方法。需要注意的是，在SQL Server 2016及其以上版本中才有STRING_SPLIT函数，而且是不支持向前兼容的，而MySQL的SUBSTRING_INDEX函数可以兼容到较老版本的MySQL，获得更好的兼容性。

二、SQL字符串交集

在使用SQL查询时，有时候需要查找多个条件的交集。这时，可以使用字符串分割函数结合SQL的INTERSECT运算符实现：

-- 使用SQL Server的STRING_SPLIT函数进行字符串分割查找交集
SELECT fruit
FROM 
(SELECT * FROM STRING_SPLIT('apple,banana,orange', ',') AS fruitTable1
INTERSECT
SELECT * FROM STRING_SPLIT('orange,banana,pear', ',') AS fruitTable2) AS resultTable

以上代码实现了使用SQL Server的STRING_SPLIT函数查找’apple,banana,orange’和’orange,banana,pear’两个字符串的交集。

三、SQL字符串分割函数split

在MySQL中，可以使用自定义的split函数进行字符串分割：

-- MySQL自定义的字符串分割函数split
DELIMITER $$

DROP FUNCTION IF EXISTS split $$

CREATE FUNCTION split(str VARCHAR(255), delimiters VARCHAR(12), pos INT)
RETURNS VARCHAR(255)
BEGIN
    DECLARE ret VARCHAR(255);
    DECLARE posStart INT UNSIGNED;
    DECLARE posEnd INT UNSIGNED;
    SET posStart = 1;
    SET posEnd = LOCATE(delimiters, str, posStart);
    SET ret = SUBSTRING(str, posStart, posEnd - posStart);
    WHILE posEnd > 0 AND pos < LENGTH(str) DO
        SET posStart = posEnd + 1;
        SET posEnd = LOCATE(delimiters, str, posStart);
        SET pos = pos + 1;
        SET ret = SUBSTRING(str, posStart, posEnd - posStart);
    END WHILE;
    RETURN ret;
END$$

DELIMITER ;

-- 调用split函数进行字符串分割并查询
SELECT split('apple,banana,orange', ',', 1) AS fruit1,
       split('apple,banana,orange', ',', 2) AS fruit2,
       split('apple,banana,orange', ',', 3) AS fruit3;

以上代码实现了一个MySQL自定义的字符串分割函数split，并演示了使用split函数进行字符串分割查询的方法。

四、SQL分割字符串

除了使用内置的字符串分割函数和自定义的split函数，还可以使用一些SQL的内置函数进行字符串分割。MySQL中的SUBSTRING_INDEX函数和Oracle中的REGEXP_SUBSTR函数就是很好的例子：

-- MySQL字符串分割函数SUBSTRING_INDEX
SELECT SUBSTRING_INDEX('apple,banana,orange', ',', 1) AS fruit1,
       SUBSTRING_INDEX(SUBSTRING_INDEX('apple,banana,orange', ',', 2), ',', -1) AS fruit2,
       SUBSTRING_INDEX('apple,banana,orange', ',', -1) AS fruit3;

-- Oracle字符串分割函数REGEXP_SUBSTR
SELECT REGEXP_SUBSTR('apple,banana,orange', '[^,]+', 1, 1) AS fruit1,
       REGEXP_SUBSTR('apple,banana,orange', '[^,]+', 1, 2) AS fruit2,
       REGEXP_SUBSTR('apple,banana,orange', '[^,]+', 1, 3) AS fruit3
FROM dual;

以上代码演示了使用MySQL和Oracle内置的字符串分割函数进行字符串分割的方法。

五、SQL字符串分割成数组

在一些特殊的场景中，还需要将SQL查询中的分割结果转化为数组，以便进行后续处理。这时，可以使用MySQL的JSON函数或者Oracle的LISTAGG函数结合PL/SQL语句实现：

-- MySQL的JSON函数将SQL查询结果转为数组
SELECT JSON_ARRAY('apple', 'banana', 'orange') AS fruitArray;
SELECT JSON_ARRAYAGG(fruit) AS fruitArray
FROM
(SELECT * FROM STRING_SPLIT('apple,banana,orange', ',')) AS fruitTable;

-- Oracle的LISTAGG函数将SQL查询结果转化为数组
DECLARE
    TYPE fruit_table_type IS TABLE OF VARCHAR2(100);
    fruit_table fruit_table_type;
BEGIN
    SELECT LISTAGG(fruit, ';') WITHIN GROUP (ORDER BY fruit)
    INTO fruit_table
    FROM
    (SELECT * FROM REGEXP_SUBSTR('apple,banana,orange', '[^,]+', 1, LEVEL) AS fruit
    FROM DUAL
    CONNECT BY LEVEL <= REGEXP_COUNT('apple,banana,orange', ',') + 1);
END;

以上代码演示了使用MySQL和Oracle将SQL查询结果转化为数组的方法。

六、string字符串分割

除了SQL内置的字符串分割函数之外，还可以使用一些外部的库或者工具进行字符串分割。这里，推荐js的string.js库，它提供了非常强大的字符串处理函数：

// string.js库的split函数进行字符串分割
var fruits = 'apple,banana,orange'.split(',');
console.log(fruits);

以上代码演示了使用string.js库的split函数进行字符串分割的方法。

七、MySQL分割字符串

MySQL在处理字符串分割时，还有另外一些常用的函数。比如，在查询一个字段中的含有特定关键字的多个值时，可以使用LOCATE和SUBSTRING_INDEX两个函数进行分割：

-- MySQL函数进行字符串分割
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(msgBody,'|',1),'|',-1) AS msg_content_1,
       SUBSTRING_INDEX(SUBSTRING_INDEX(msgBody,'|',2),'|',-1) AS msg_content_2,
       SUBSTRING_INDEX(SUBSTRING_INDEX(msgBody,'|',3),'|',-1) AS msg_content_3
FROM table_name
WHERE LOCATE("keyword",msgBody) > 0

以上代码实现了使用MySQL的LOCATE和SUBSTRING_INDEX函数进行字符串分割查询的方法，并配合WHERE语句控制查询条件。

八、SQL分割字符串并查询

如果需要在SQL查询中同时进行字符串分割和查询，可以使用一些特殊的语句或者函数。比如，在Oracle中，可以使用CONNECT BY LEVEL和SUBSTR函数实现：

-- Oracle使用CONNECT BY LEVEL和SUBSTR实现字符串分割并查询
SELECT SUBSTR('apple,banana,orange', pos_start, pos_end-pos_start) AS fruit
FROM
(SELECT LEVEL as pos,
        pos_start,
        INSTR(str, ',', pos_start, 1) as pos_end
FROM
(SELECT ',apple,banana,orange,' AS str, 1 as pos_start FROM dual)
CONNECT BY
pos_start <= LENGTH(str) AND LEVEL  0;

以上代码实现了在Oracle中使用CONNECT BY LEVEL和SUBSTR函数实现字符串分割并查询的方法。

九、SQL分割字符串函数split

最后，再介绍一个使用JavaScript编写的字符串分割函数split。这个函数可以在需要时直接在HTML代码中引入，也可以作为一个独立的JS文件使用：

// JavaScript编写的字符串分割函数split
function split(str, delimiter) {
    var pieces = str.split(delimiter);
    for (var i=0; i<pieces.length; i++) {
        pieces[i] = pieces[i].trim();
    }
    return pieces;
}

// 使用split函数进行字符串分割
var fruits = split('apple,  banana,    orange', ',');
console.log(fruits);

以上代码演示了如何使用由JavaScript编写的字符串分割函数split进行字符串分割的方法。