SQL string处理：按逗号拆分成列|极客教程

SQL string处理：按逗号拆分成列

在实际的数据处理过程中，我们经常会遇到将包含多个值的字符串按照特定规则进行拆分的情况。比如，在数据库表中的某一列中存储了多个值，这些值之间用逗号进行了分隔，我们需要将这些值按照逗号进行拆分成单独的列。在本文中，我将介绍如何使用SQL来实现这个功能。

1. 使用`STRING_SPLIT`函数（SQL Server）

从SQL Server 2016开始，Microsoft引入了STRING_SPLIT函数，这个函数可以根据指定的分隔符将字符串拆分成多个值，并返回一个表。

假设我们有一个表Products，其中的categories列存储了多个分类，以逗号进行分隔。现在，我们想要将这些分类按照逗号进行拆分成单独的列。

CREATE TABLE Products (
    id INT,
    name VARCHAR(100),
    categories VARCHAR(100)
);

INSERT INTO Products VALUES 
(1, 'Product A', 'Electronics, Gadgets'),
(2, 'Product B', 'Clothing'),
(3, 'Product C', 'Books, Stationery, Electronics');

使用STRING_SPLIT函数来实现：

SELECT id, name, value AS category
FROM Products
CROSS APPLY STRING_SPLIT(categories, ',');

运行结果：

id | name      | category
---------------------
1  | Product A | Electronics
1  | Product A | Gadgets
2  | Product B | Clothing
3  | Product C | Books
3  | Product C | Stationery
3  | Product C | Electronics

2. 使用`regexp_split_to_table`函数（PostgreSQL）

如果你在使用PostgreSQL数据库，可以使用regexp_split_to_table函数来实现类似的功能。该函数接受一个正则表达式作为参数，可以按照正则表达式将字符串拆分成多个值。

继续以上面的示例，我们将使用regexp_split_to_table函数来拆分categories列：

SELECT id, name, category
FROM Products,
     regexp_split_to_table(categories, ',') AS category;

运行结果：

id | name      | category
---------------------
1  | Product A | Electronics
1  | Product A | Gadgets
2  | Product B | Clothing
3  | Product C | Books
3  | Product C | Stationery
3  | Product C | Electronics

3. 使用自定义函数（通用方法）

如果你使用的数据库不支持上述方法，你可以编写自定义函数来实现字符串拆分功能。以下是一个示例代码，展示了如何在MySQL数据库中实现：

CREATE FUNCTION SPLIT_STRING(str VARCHAR(255), delim VARCHAR(12), pos INT)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, pos),
                          LENGTH(SUBSTRING_INDEX(str, delim, pos - 1)) + 1),
               delim, '');

SELECT id, name,
       SPLIT_STRING(categories, ',', pos) AS category
FROM Products,
     (SELECT 1 AS pos
      UNION SELECT 2 
      UNION SELECT 3) AS positions;

运行结果：

id | name      | category
---------------------
1  | Product A | Electronics
1  | Product A | Gadgets
2  | Product B | Clothing
3  | Product C | Books
3  | Product C | Stationery
3  | Product C | Electronics