在SQL中将CSV字段拆分为不同的行 [英] Split a CSV field into different rows in SQL

问题描述

我的一个同事在处理COBOL程序时遇到了此问题，并最终在应用程序级别解决了该问题.我仍然很好奇，是否有可能使用SQL在数据访问级别上解决它.这与

A colleague of mines encountered this problem while working on a COBOL program and eventually solved it at the application level. Still I am curious if it is possible to solve it on the data access level, with SQL. This is somehow related to this other question, but I'd like to use only ANSI SQL.

我正在寻找一个对包含可变长度CSV行的VARCHAR字段起作用的SQL选择查询.该查询的目的是在其自己的结果集行中拆分每个CSV字段.

I'm looking for a single SQL select query that acts on a VARCHAR field that contains variable length CSV rows. The purpose of the query is to split every CSV field in its own result set row.

这是一个包含架构和数据的示例(这是 fiddle ):

Here is an example with schema and data (and here is the fiddle):

CREATE TABLE table1 (`field` varchar(100));

 INSERT INTO table1 (`field`)
      VALUES
             ('Hello,world,!')    ,
             ('Haloa,!')          ,
             ('Have,a,nice,day,!');

这是我想从查询中获得的输出:

Here is the output I'd like to have from the query:

Hello
world
!
Haloa
!
Have
a
nice
day
!

使用的CSV分隔符是逗号，现在我不必担心转义.

The CSV separator used is the comma, and for now I wouldn't worry about escaping.

推荐答案

据我所知，这是ANSI SQL:

As far as I can tell, this is ANSI SQL:

with recursive word_list (field, word, rest, field_id, level) as (             
  select field, 
         substring(field from 1 for position(',' in field) - 1) as word,
         substring(field from position(',' in field) + 1) as rest,
         row_number() over () as field_id,
         1
  from table1
  union all 
  select c.field, 
         case 
            when position(',' in p.rest) = 0 then p.rest
            else substring(p.rest from 1 for position(',' in p.rest) - 1) 
         end as word,
         case 
            when position(',' in p.rest) = 0 then null
            else substring(p.rest from position(',' in p.rest) + 1) 
         end as rest, 
         p.field_id,
         p.level + 1
  from table1 as c
    join word_list p on c.field = p.field and position(',' in p.rest) >= 0
) 
select word
from word_list
order by field_id, level;
     

这假定 field 中的值是唯一的.

This assumes that the values in field are unique.

这是一个运行示例

这篇关于在SQL中将CSV字段拆分为不同的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！