none
Spliting/parsing strings in a query RRS feed

  • Question

  • Current                          A1                           A    B    C    D
    10 20 30 40          10, 20, 30, 40                  10   20  30  40

    50 60 70 80          50, 60, 70, 80                  50   60  70  80

    90 15 25 35          90, 15, 25, 35                  90   15  25  35

    I would like to split/parse the strings in the current table into another column separated by commas like in A1.

    Additionally, if possible, I would like to split/parse the strings in the current table into separate columns like A, B, C, D.

    Is this possible?  I have attempted use the Split(), but I have gotten nowhere.  Can you help?

    Friday, January 19, 2018 3:35 AM

Answers

  • To offer one more potential solution, if your numbers are consistently separated by spaces, then I'd turn to using the Split() function.  The issue being, that a query cannot natively use it, so we have to first create a public function and call it in a query.

    So create a new Standard Module, or use an existing standard module, and insert the following function

    Public Function MySplit(sInput As String, sDelim As String, iPos As Integer) As String
        On Error GoTo Error_Handler
    
        MySplit = Split(sInput, sDelim)(iPos)
    
    Error_Handler_Exit:
        On Error Resume Next
        Exit Function
    
    Error_Handler:
        MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: MySplit" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occured!"
        Resume Error_Handler_Exit
    End Function

    Then we can create a simple query using a syntax like:

    SELECT myTable.Current, Replace([Current]," ",", ") AS A1, MySplit([Current]," ",0) AS A, MySplit([Current]," ",1) AS B, MySplit([Current]," ",2) AS C, MySplit([Current]," ",3) AS D
    FROM myTable;
    

    which give us

    The benefit, as illustrated by the last row of data, is that it doesn't rely on your numbers always being 2 characters in size.  It is adaptive.  As long as there is a consistent separator between the values, it will work.

    Just one more option to consider.


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Friday, January 19, 2018 5:56 PM

All replies

  • This would be fairly easy to do if you are willing to have your query call a custom VBA function.  Are you willing to do that, or do you require a SQL-only solution?  In a VBA function, you can do the splitting and joining that SQL won't let you do.

    Can you rely on the source field always having exactly 4 entries?  Or no more than 4?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, January 19, 2018 4:30 AM
  • What do these values represent?  Having multiple values in a single column in a row in a table means the table is not normalized to First Normal Form (1NF), as does having values of the same attribute in multiple columns.  First Normal Form is defined formally as follows:

    First Normal Form: A relvar is in 1NF if and only if, in every legal value of that relvar, every tuple contains exactly one value for each attribute.

    Loosely speaking, in the language of the relational model, a relvar (relation variable) equates to a table, a tuple to a row (record) and an attribute to a column (field).

    Consequently the table should almost certainly be decomposed into two related tables, with the referencing table containing a foreign key column referencing the primary key of the current table, and a single column to hold the values.  So with four values per row in the current table the referencing table would have four rows per row in the current table, with each subset of four rows referencing the primary key of the same row in the current table.

    Having created the referencing table, the data from its single current column can be parsed and rows inserted into the referencing table by means of VBA code which loops through a recordset of the current table's rows.  Following the successful decomposition of the table the current multi-values column can be deleted.  

    Ken Sheridan, Stafford, England

    Friday, January 19, 2018 4:55 PM
  • If your data is consistent.


    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.

    Friday, January 19, 2018 5:44 PM
  • To offer one more potential solution, if your numbers are consistently separated by spaces, then I'd turn to using the Split() function.  The issue being, that a query cannot natively use it, so we have to first create a public function and call it in a query.

    So create a new Standard Module, or use an existing standard module, and insert the following function

    Public Function MySplit(sInput As String, sDelim As String, iPos As Integer) As String
        On Error GoTo Error_Handler
    
        MySplit = Split(sInput, sDelim)(iPos)
    
    Error_Handler_Exit:
        On Error Resume Next
        Exit Function
    
    Error_Handler:
        MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: MySplit" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occured!"
        Resume Error_Handler_Exit
    End Function

    Then we can create a simple query using a syntax like:

    SELECT myTable.Current, Replace([Current]," ",", ") AS A1, MySplit([Current]," ",0) AS A, MySplit([Current]," ",1) AS B, MySplit([Current]," ",2) AS C, MySplit([Current]," ",3) AS D
    FROM myTable;
    

    which give us

    The benefit, as illustrated by the last row of data, is that it doesn't rely on your numbers always being 2 characters in size.  It is adaptive.  As long as there is a consistent separator between the values, it will work.

    Just one more option to consider.


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Friday, January 19, 2018 5:56 PM
  • To offer one more potential solution, if your numbers are consistently separated by spaces, then I'd turn to using the Split() function.  The issue being, that a query cannot natively use it, so we have to first create a public function and call it in a query.

    Daniel, that is exactly what I was getting at, but I wanted to find out if the OP would allow it.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, January 19, 2018 6:10 PM
  • I hear you.

    Actual, I think you taught me this technique many a moons ago!


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Friday, January 19, 2018 6:21 PM
  • Yes, I would be willing.
    Friday, January 19, 2018 6:46 PM
  • I understand that it is not normalized.  It is imported and I want separate the data.
    Friday, January 19, 2018 6:47 PM
  • This looks like the solution, but I must say the responses have been great.  I will try them all just to practice other ideas.  All of you, thanks so much for your help.
    Friday, January 19, 2018 6:52 PM
  • In case anyone's interested, here's a version that may be more efficient if it might be called multiple times to split the same source string:

    Function fncGetToken( _
            sourceString As Variant, _
            delimiter As String, _
            ByVal tokenNumber As Integer) _
        As Variant
        
        ' Return the <tokenNumber>th token in <sourceString>, where
        ' tokens are delimited by <delimiter>.
        '
        ' If an error is detected, return a Variant(Error) with the error number
        ' If there is no token number <tokenNumber>, return Null.
        
        On Error GoTo Err_Handler
        
        Static strSource    As String
        Static astrTokens() As String
        Dim lngPos          As Long
        Dim intCount        As Integer
        
        fncGetToken = Null  ' intially
        
        ' Handle exceptional conditions.
        If tokenNumber < 1 Then
            fncGetToken = CVErr(5)
            Exit Function
        End If
        If Len(delimiter) < 1 Then
            fncGetToken = CVErr(5)
            Exit Function
        End If
        If IsNull(sourceString) Then
            Exit Function
        End If
    
        ' If the source has changed, split the source into a new static array
        If sourceString <> strSource Then
            strSource = sourceString
            astrTokens = Split(strSource, delimiter, , vbBinaryCompare)
        End If
        
        tokenNumber = tokenNumber - 1   ' for zero-based array
        
        If tokenNumber > UBound(astrTokens) Then
            fncGetToken = Null
        Else
            fncGetToken = astrTokens(tokenNumber)
        End If
        
    Exit_Point:
        Exit Function
        
    Err_Handler:
        fncGetToken = CVErr(Err.Number)
        Resume Exit_Point
    
    End Function
    


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, January 19, 2018 7:19 PM
  • I understand that it is not normalized.  It is imported and I want separate the data.

    Having parsed the data into four separate columns, using the techniques described in this thread, if, as I suspect, data is then being encoded as column headings, it’s a relatively simple task to decompose the table.  The basis of this would be a 4-part UNION query of which each part returns the primary key of the table, the value from one of the columns, and a constant of the data value being encoded by the column heading.  It's then merely a case of creating an 'append' query based on the UNION query to insert the data into a normalized table of three columns, of which the foreign key and the column into which the constants have been inserted would be its composite primary key.

    Ken Sheridan, Stafford, England

    Friday, January 19, 2018 9:18 PM