SQL Split列基于1个或多个可能的定界符，并插入到新表中 [英] SQL Split column based on 1 or more possible delimiter and insert in new table

问题描述

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

I m currently working on a SQl in MS-Access 2010 that can split a column based on a delimiter(,). In the column where I want to split on can have zero, one, two or three delimiter. I found out how i can split the column if there is only one delimiter (see SQL at the end of the question) but not if there are multiple delimiters.

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

The SQL is based on the following table. This table is filled with possible data that can occur in the table.

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

我想以创建新表的方式拆分值"列.列"ID"相同是没有问题的，因为这将不是PK.

I want to split the column "value" in a way that i create a new table like this. It is not a problem that the column "ID" is the same because this will not be the PK.

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

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

I tried to alter the SQL from this question but it only works when there is only 1 delimiter(,) due to the fact is uses the function LEFT and MID. I cant find how to alter it in a way that i can split if there are more then 1 delimiter in the column. The SQL i used to split if there is one delimiter:

  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;

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

Does somebody knows how to split a column if there is more then one delimiter?

推荐答案

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

To split and obtain a specific value, I prefer to use a user-defined function.

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调整为以下内容:

After this, you can adjust your SQL to the following:

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解决方案，请允许我向您演示如何实现此目标，以及为什么这是一个糟糕的计划.

If you really want an all-SQL solution, let me demonstrate to you how this can be achieved, and why this is a bad plan.

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

For this example, I've written the following code to automatically generate the appropriate SQL expression

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

Let's use the example to generate a simple split: I want the 6th element of the following string: 1,2,3,4,5,6,7

要在立即窗口中生成字符串，请执行以下操作:

To generate the string, in the immediate window:

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

产生以下表达式以返回该字符串的第6个元素(仅适用于SQL):

Results in the following expression to return the 6th element of that string (SQL only):

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)

Append SELECT to the start of that, and execute it as a query, and it returns 6, as expected. Only you have a totally horrid query, while with the UDF you would just have SELECT SplitString("1,2,3,4,5,6,7", ",", 6)

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

You can, of course, use GenerateSQLSplit to create the query (I made sure it returned an empty string if the item was not in the string, so you can use that to test if an nth element exists). I do not recommend it, though, because the query will be long, inefficient and hard to maintain.

这篇关于SQL Split列基于1个或多个可能的定界符，并插入到新表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！