SQL Split列基于1个或多个可能的定界符，并插入到新表中

一尘不染

SQL Split列基于1个或多个可能的定界符，并插入到新表中

sql

我目前正在研究MS-Access
2010中的SQl，它可以基于delimiter（，）拆分列。在我要分割的列中可以有零，一，二或三个定界符。我发现如果只有一个定界符（请参阅问题末尾的SQL），但如果有多个定界符，我将如何拆分列。

SQL基于下表。该表填充了表中可能出现的可能数据。

ID         column_value
---------------------
1          2, 44
2          1
3          8, 9, 4
4          7

我想要以这种方式创建新表的方式来拆分“值”列。列“ ID”相同是没有问题的，因为这将不是PK。

ID         value
---------------------
1          2
1          44
2          1
3          8
3          9
3          4
4          7

我试图从这个问题更改SQL，但是它仅在只有1个delimiter（，）时起作用，因为事实是使用了LEFT和MID函数。如果列中的分隔符超过1个，我找不到如何以一种可以拆分的方式进行更改。如果有一个定界符，我通常使用该SQL进行拆分：

  select * into importeddata
from (SELECT column_value, id
  FROM SourceData
  WHERE InStr(column_value, ',') = 1
  UNION ALL
  SELECT Left(column_value, InStr(column_value, ',') - 1), id
  FROM SourceData
  WHERE InStr(column_value, ',') > 0
  UNION ALL
  SELECT mid(column_value, InStr(column_value, ',')+1 ), id
  FROM SourceData
  WHERE InStr(column_value, ',') > 0) AS CleanedUp;

如果有多个分隔符，有人知道如何拆分一列吗？

阅读 165

2021-03-17

共1个答案

一尘不染

要拆分并获取特定值，我更喜欢使用用户定义的函数。

Public Function SplitString(str As String, delimiter As String, count As Integer) As String
    Dim strArr() As String
    strArr = Split(str, delimiter, count + 1)
    count = count - 1 'zero-based
    If UBound(strArr) >= count Then
        SplitString = strArr(count)
    End If
End Function

之后，您可以将SQL调整为以下内容：

SELECT * INTO importeddata
FROM (
SELECT SplitString(column_value, ',', 1), id
FROM SourceData
WHERE SplitString(column_value, ',', 1) <> ''
UNION ALL
SELECT SplitString(column_value, ',', 2), id
FROM SourceData
WHERE SplitString(column_value, ',', 2) <> ''
UNION ALL
SELECT SplitString(column_value, ',', 3), id
FROM SourceData
WHERE SplitString(column_value, ',', 3) <> ''
) AS A

如果您真的想要一个全SQL解决方案，那么让我向您演示如何实现此目标，以及为什么这是一个糟糕的计划。

对于此示例，我编写了以下代码来自动生成适当的SQL表达式

Public Sub GenerateSQLSplit(str As String, Delimiter As String, Count As Integer)
    Dim i As Integer
    If Count = 1 Then
        Debug.Print "IIf(InStr(1, " & str & ", " & Delimiter & ") = -1, " & str & ", Left(" & str & ", InStr(1, " & str & ", " & Delimiter & ") - 1))"
    Else
        Dim strPrevious As String
        Dim strNext As String
        strPrevious = "InStr(1, " & str & "," & Delimiter & ")"
        i = Count - 1
        Do While i <> 1
            strPrevious = "InStr(" & strPrevious & " + Len(" & Delimiter & "), " & str & "," & Delimiter & ")"
            i = i - 1
        Loop
        strNext = "InStr(" & strPrevious & " + Len(" & Delimiter & "), " & str & " , " & Delimiter & ")"
        Debug.Print "IIf( " & strPrevious & "> 0, IIf(" & strNext & " < 1, Mid(" & str & ", " & strPrevious & " + Len(" & Delimiter & ")), Mid(" & str & ", " & strPrevious & " + Len(" & Delimiter & "), " & strNext & " - " & strPrevious & " - Len(" & Delimiter & "))), """") "
    End If
End Sub

让我们使用该示例生成一个简单的拆分：我想要以下字符串的第6个元素： 1,2,3,4,5,6,7

要生成字符串，请在立即窗口中：

GenerateSQLSplit "'1,2,3,4,5,6,7'", "','", 6

以下表达式的结果返回该字符串的第6个元素（仅SQL）：

IIf( InStr(InStr(InStr(InStr(InStr(1, '1,2,3,4,5,6,7',',') + Len(','), '1,2,3,4,5,6,7',',') + Len(','), '1,2,3,4,5,6,7',',') + Len(','), '1,2,3,4,5,6,7',',') + Len(','), '1,2,3,4,5,6,7',',')> 0, IIf(InStr(InStr(InStr(InStr(InStr(InStr(1, '1,2,3,4,5,6,7',',') + Len(','), '1,2,3,4,5,6,7',',') + Len(','), '1,2,3,4,5,6,7',',') + Len(','), '1,2,3,4,5,6,7',',') + Len(','), '1,2,3,4,5,6,7',',') + Len(','), '1,2,3,4,5,6,7' , ',') < 1, Mid('1,2,3,4,5,6,7', InStr(InStr(InStr(InStr(InStr(1, '1,2,3,4,5,6,7',',') + Len(','), '1,2,3,4,5,6,7',',') + Len(','), '1,2,3,4,5,6,7',',') + Len(','), '1,2,3,4,5,6,7',',') + Len(','), '1,2,3,4,5,6,7',',') + Len(',')), Mid('1,2,3,4,5,6,7', InStr(InStr(InStr(InStr(InStr(1, '1,2,3,4,5,6,7',',') + Len(','), '1,2,3,4,5,6,7',',') + Len(','), '1,2,3,4,5,6,7',',') + Len(','), '1,2,3,4,5,6,7',',') + Len(','), '1,2,3,4,5,6,7',',') + Len(','), InStr(InStr(InStr(InStr(InStr(InStr(1, '1,2,3,4,5,6,7',',') + Len(','), '1,2,3,4,5,6,7',',') + Len(','), '1,2,3,4,5,6,7',',') + Len(','), '1,2,3,4,5,6,7'
,',') + Len(','), '1,2,3,4,5,6,7',',') + Len(','), '1,2,3,4,5,6,7' , ',') - InStr(InStr(InStr(InStr(InStr(1, '1,2,3,4,5,6,7',',') + Len(','), '1,2,3,4,5,6,7',',') + Len(','), '1,2,3,4,5,6,7',',') + Len(','), '1,2,3,4,5,6,7',',') + Len(','), '1,2,3,4,5,6,7',',') - Len(','))), "")

追加SELECT到开头，然后将其作为查询执行6，并按预期返回。只有您有一个完全可怕的查询，而有了UDF，您将只有SELECT SplitString("1,2,3,4,5,6,7", ",", 6)

当然，您可以GenerateSQLSplit用来创建查询（如果该项目不在字符串中，那么我确保它返回一个空字符串，因此您可以使用它来测试是否存在第n个元素）。但是，我不建议这样做，因为查询时间长，效率低且难以维护。

2021-03-17