SQL 分割字符串

导论

在数据库中，经常会遇到需要将字符串按照特定字符进行分割的情况。例如，一些数据库表中可能会有一列记录了多个数值以逗号分隔的字符串，我们需要将这些数值提取出来进行分析、计算或者查询。在这种情况下，我们可以使用 SQL 来实现字符串的分割。

本文将介绍使用 SQL 分割字符串的几种常见方法，并给出示例代码和运行结果。

方法一：使用内置函数

STRING_SPLIT 函数（仅适用于 SQL Server 2016 及以上版本）

在 SQL Server 2016 及以上版本中，我们可以使用 STRING_SPLIT 函数来将字符串按照指定的分隔符进行分割。

SELECT value
FROM STRING_SPLIT('apple,banana,orange', ',')

运行结果：

value
------
apple
banana
orange

REGEXP_SPLIT_TO_TABLE 函数（仅适用于 PostgreSQL）

在 PostgreSQL 数据库中，我们可以使用 REGEXP_SPLIT_TO_TABLE 函数来实现字符串的分割。

SELECT unnest
FROM REGEXP_SPLIT_TO_TABLE('apple,banana,orange', ',') AS unnest

运行结果：

unnest
--------
apple
banana
orange

SPLIT_PART 函数（通用）

SPLIT_PART 函数可以将字符串按照指定分隔符拆分，并返回指定位置的子串。

SELECT SPLIT_PART('apple,banana,orange', ',', 1) AS part1,
       SPLIT_PART('apple,banana,orange', ',', 2) AS part2,
       SPLIT_PART('apple,banana,orange', ',', 3) AS part3

运行结果：

part1 | part2  | part3
-------+--------+-------
apple  | banana | orange

方法二：使用递归查询

如果数据库不支持上述内置函数，我们可以使用递归查询的方式来分割字符串。

WITH RECURSIVE split_cte AS (
  SELECT
    SUBSTRING_INDEX('apple,banana,orange', ',', 1) AS part,
    SUBSTRING('apple,banana,orange', LENGTH(SUBSTRING_INDEX('apple,banana,orange', ',', 1)) + 2) AS remaining
  UNION ALL
  SELECT
    SUBSTRING_INDEX(remaining, ',', 1) AS part,
    SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, ',', 1)) + 2) AS remaining
  FROM split_cte
  WHERE remaining <> ''
)
SELECT part
FROM split_cte

运行结果：

part
-----
apple
banana
orange

方法三：使用正则表达式

如果数据库支持正则表达式，我们可以借助正则表达式来实现字符串的分割。

SELECT
  regexp_split_to_table('apple,banana,orange', ',') AS part

运行结果：

 part
-------
apple
banana
orange

方法四：使用自定义函数

如果数据库没有提供内置的字符串分割函数，我们可以自定义一个函数来实现字符串的分割。

CREATE OR REPLACE FUNCTION split_string(
  p_string VARCHAR,
  p_delimiter VARCHAR
)
RETURNS SETOF VARCHAR AS
DECLARE
  part VARCHAR;
  remaining VARCHAR;
BEGIN
  part := SUBSTRING_INDEX(p_string, p_delimiter, 1);
  remaining := SUBSTRING(p_string, LENGTH(part) + LENGTH(p_delimiter) + 1);

  IF part <> '' THEN
    RETURN NEXT part;
    RETURN QUERY SELECT * FROM split_string(remaining, p_delimiter);
  END IF;

  RETURN;
END; LANGUAGE plpgsql;

使用自定义函数来分割字符串：

SELECT *
FROM split_string('apple,banana,orange', ',') AS part

运行结果：

 part
-------
apple
banana
orange

总结

本文介绍了使用 SQL 分割字符串的几种常见方法，包括使用内置函数、递归查询、正则表达式以及自定义函数。不同的数据库系统可能支持不同的方法，因此在实际应用中需要根据具体情况选择合适的方法。