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

查看：22
本文介绍了基于 1 个或多个可能的分隔符的 SQL 拆分列并插入新表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我目前正在研究 MS-Access 2010 中的 SQL，它可以根据分隔符 (,) 拆分列.在我想拆分的列中可以有零、一、二或三个分隔符.我发现了如何在只有一个分隔符的情况下拆分列(请参阅问题末尾的 SQL)，但如果有多个分隔符则不行.

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

ID column_value---------------------1 2, 442 13 8, 9, 44 7

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

ID 值---------------------1 21 442 13 83 93 44 7

我试图从这个问题改变SQL但它仅在只有 1 个分隔符 (,) 时才有效，因为它使用了函数 LEFT 和 MID.如果列中的分隔符超过 1 个，我无法找到如何以可以拆分的方式更改它.如果有一个分隔符，我用来拆分的 SQL:

 select * into importdatafrom (SELECT column_value, id从源数据WHERE InStr(column_value, ',') = 1联合所有SELECT Left(column_value, InStr(column_value, ',') - 1), id从源数据WHERE InStr(column_value, ',') >0联合所有SELECT mid(column_value, InStr(column_value, ',')+1 ), id从源数据WHERE InStr(column_value, ',') >0) 作为清理；

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

解决方案

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

公共函数SplitString(str As String, delimiter As String, count As Integer) As StringDim strArr() 作为字符串strArr = Split(str, delimiter, count + 1)count = count - 1 '从零开始如果 UBound(strArr) >= count 那么SplitString = strArr(count)万一结束函数

在此之后，您可以将 SQL 调整为以下内容:

SELECT * INTO 导入数据从 (SELECT SplitString(column_value, ',', 1), id从源数据WHERE SplitString(column_value, ',', 1) <>''联合所有SELECT SplitString(column_value, ',', 2), id从源数据WHERE SplitString(column_value, ',', 2) <>''联合所有SELECT SplitString(column_value, ',', 3), id从源数据WHERE SplitString(column_value, ',', 3) <>'') 作为一个

如果您真的想要一个全 SQL 的解决方案，让我向您展示如何实现这一点，以及为什么这是一个糟糕的计划.

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

Public Sub GenerateSQLSplit(str As String, Delimiter As String, Count As Integer)Dim i 作为整数如果计数 = 1 那么Debug.Print "IIf(InStr(1, " & str & ", " & Delimiter & ") = -1, " & str & ", Left(" & str & ", InStr(1, " & str & ", " & Delimiter & ") - 1))"别的Dim strPrevious As StringDim strNext 作为字符串strPrevious = "InStr(1, " & str & "," & Delimiter & ")"我 = 计数 - 1当我 <>1strPrevious = "InStr(" & strPrevious & " + Len(" & Delimiter & "), " & str & "," & Delimiter & ")"我 = 我 - 1环形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 & "))), """") "万一结束子

让我们使用示例生成一个简单的拆分:我想要以下字符串的第 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 个元素存在).不过，我不推荐这样做，因为查询会很长、效率低且难以维护.

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.

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

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

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

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

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.

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

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

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(','))), "")

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)

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.

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

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆