ms access - SQL Split column based on 1 or more possible delimiter and insert in new table

Question

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?

Answer 1

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.