sql - 在不打开文件的情况下从 csv 文件的单个列中删除分隔符

Question

我有以下csv文件的内容

CSV 文件的内容如下:

Date_Added|this_flag|Name|DOB|SSN|ID

2015 年 5 月 1 日|Y|Jingle|heimerscmidt|19901002|123456789|3

May 1st, 2015|N|Jingleheimerscmidt|19901002|123456789|3
May 5th, 2015|Y|Jon|19901001|012345678|1
May 1st, 2015|N|Jon|19901002|012345678|1
May 1st, 2015|Y|Jacob|19901001|234567890|2
May 5th, 2015|N|Jingleheimerscmidt|19901001|123456789|3
May 1st, 2015|Y|Jingleheimerscmidt|19901001|123456789|3

正如您在粗体和斜体内容中看到的，除了管道运算符分隔列之外，内容中还有一个管道运算符。我想在不打开 csv 文件的情况下从文本中删除该管道运算符。有没有办法通过编写代码或任何其他方法来解决这个问题

Answer 1

好的，我知道您标记了 oracle，因此也许您自己或其他 Oracle 大师可以从 sql-server 迁移此解决方案。我知道 oracle 能够执行这些操作中的每一个。

通常我会说你想要一种快速/奇特的方式来分割字符串，但在这种情况下你需要在分隔符之间保持字符串的顺序位置。所以我想到了一种方法。

1) 首先将 CSV 作为所有 1 列导入到临时表中。现在，如果您的 CRLF 也在名称列中找到，这将是一个问题....但我们假设这不是因为您没有指定它。

2) 在该表上构建一个 row_number 以用作假主键，并确定何时有比应有的更多的分隔符。

3) 使用递归 cte 将字符串溢出到行中，并保持子字符串在原始字符串中的顺序位置，以便稍后连接。

4) 通过 MergePositions 改变 OrdinalPostion 并基于它生成 DENSE_RANK() 来确定要分组的行

5) Conditional Aggregation 使用 OrdinalGroup 作为列号，然后使用连接方法合并所有 OrdginalGroup 3 行。

DECLARE @CSV as TABLE (LumpedColumns NVARCHAR(MAX))
INSERT INTO @CSV VALUES
('May 1st, 2015|Y|Jingle|he|imerscmidt|19901002|123456789|3')
,('May 1st, 2015|N|Jingleheimerscmidt|19901002|123456789|3')
,('May 5th, 2015|Y|Jon|19901001|012345678|1')
,('May 1st, 2015|N|Jon|19901002|012345678|1')
,('May 1st, 2015|Y|Jacob|19901001|234567890|2')
,('May 5th, 2015|N|Jingleheimerscmidt|19901001|123456789|3')
,('May 1st, 2015|Y|Jingleheimerscmidt|19901001|123456789|3')

;WITH cteFakePrimaryKey AS (
    SELECT
       LumpedColumns
       ,CASE WHEN LEN(LumpedColumns) - LEN(REPLACE(LumpedColumns,'|','')) > 5 THEN
          LEN(LumpedColumns) - LEN(REPLACE(LumpedColumns,'|','')) - 5 ELSE 0 END as MergeXPositions
       ,ROW_NUMBER() OVER (ORDER BY (SELECT 0)) as PK
    FROM
       @CSV
)


, cteRecursive AS (
    SELECT
       PK
       ,LumpedColumns
       ,MergeXPositions
       ,LEFT(LumpedColumns,CHARINDEX('|',LumpedColumns)-1) as ColValue
       ,RIGHT(LumpedColumns,LEN(LumpedColumns) - CHARINDEX('|',LumpedColumns)) as Remaining
       ,1 As OrdinalPosition
    FROM
       cteFakePrimaryKey

    UNION ALL

    SELECT
       PK
       ,LumpedColumns
       ,MergeXPositions
       ,LEFT(Remaining,CHARINDEX('|',Remaining)-1)
       ,RIGHT(Remaining,LEN(Remaining) - CHARINDEX('|',Remaining))
       ,OrdinalPosition + 1
    FROM
       cteRecursive
    WHERE
       Remaining IS NOT NULL AND CHARINDEX('|',Remaining) > 0

    UNION ALL

    SELECT 
       PK
       ,LumpedColumns
       ,MergeXPositions
       ,Remaining
       ,NULL
       ,OrdinalPosition + 1
    FROM
       cteRecursive
    WHERE Remaining IS NOT NULL AND CHARINDEX('|',Remaining) = 0
)

, cteOrdinalGroup AS (
    SELECT
       PK
       ,LumpedColumns
       ,ColValue
       ,OrdinalPosition
       ,DENSE_RANK() OVER (PARTITION BY PK ORDER BY
          CASE
             WHEN OrdinalPosition < 3 THEN OrdinalPosition
             WHEN OrdinalPosition > (3 + MergeXPositions) THEN OrdinalPosition
          ELSE 3 END ) as OrdinalGRoup

    FROM
       cteRecursive
)

SELECT
    PK
    ,LumpedColumns
    ,MAX(CASE WHEN OrdinalGRoup = 1 THEN ColValue END) as Date_Added
    ,MAX(CASE WHEN OrdinalGRoup = 2 THEN ColValue END) as this_flag
    ,STUFF(
        (SELECT '|' + ColValue
        FROM
            cteOrdinalGroup g2
        WHERE
             g1.PK = g2.PK
             AND g2.OrdinalGroup = 3
        ORDER BY
          g2.OrdinalPosition
        FOR XML PATH(''))
        ,1,1,'') as name
    ,MAX(CASE WHEN OrdinalGRoup = 4 THEN ColValue END) as DOB
    ,MAX(CASE WHEN OrdinalGRoup = 5 THEN ColValue END) as SSN
    ,MAX(CASE WHEN OrdinalGRoup = 6 THEN ColValue END) as ID
FROM
    cteOrdinalGroup g1
GROUP BY
    PK
    ,LumpedColumns
ORDER BY
    PK