query to split concatenated string into separate parts

[ankur_bhardwaj]

Need your expertise in one query: I need a SQL query to split concatenated string into separate parts. My data looks like this Name Location Name Link Location Link Final Location Material C50 2398::3276::3916 GA-4-2 58::52::24 558::352::324::70 93530and i want output like this: Name Location Name Link Location Link Final Location Material C50 2398 GA-4-2 58 558 93530 C50 3276 GA-4-2 52 352 93530 C50 3916 GA-4-2 24 324 93530 C50 GA-4-2 70 93530

[CJ_London]

pretty sure you wont be able to do that in SQL, You'll need to use VBA. You will also need to explain the rules - looks like each 'section' is separated by a space, then some sections are separated with ::. To get these into their individual elements is easily handled with a split function. However what is not clear is are the number of elements in each section always the same i.e. 3,3,2 or can they vary?

[ankur_bhardwaj]

pretty sure you wont be able to do that in SQL, You'll need to use VBA. You will also need to explain the rules - looks like each 'section' is separated by a space, then some sections are separated with ::. To get these into their individual elements is easily handled with a split function. However what is not clear is are the number of elements in each section always the same i.e. 3,3,2 or can they vary?

@Ajax Thanks for your reply. I don't have any idea about VBA. I am trying to implement this in MS access. Can you suggest me a direct query for that. I am trying to implement it using String functions like MID,Left,Right but still no luck. Your help is highly appreciated.
Thanks !

[orange]

Please respond to Ajax's question regarding the consistency of format.
I think you might consider building a function, but that depends on the "degree of standardization in the format of your input.
Do you have any options to change the input?

[ankur_bhardwaj]

@ajax Number of elements in each section can vary.

[CJ_London]

don't have any idea about VBA. I am trying to implement this in MS access

VBA is used extensively in Access

Number of elements in each section can vary.

in that case, it cannot be done in a single query - best you could is to write separate queries for every possible combination of elements for each row - using left, right mid and instr functions

However I can't suggest what this would look like without you clarifying the following:

1. Your presentation of data is not clear as to what heading relates to what element - layout your requirement like this, ideally without spaces in field names and use . or _ rather than spaces - multiple spaces are removed from the text.
Name..LocationName....Link.........Location..LinkF inaLocation..Material
C50....2398.................GA-4-2.....58..........558....................93530

and name is a reserved word, change it to something more meaningful

2. whether the length of elements can vary. e.g. is the first column (C50) always 3 characters and the last column 5 characters or can that vary as well?

3. You also need to clarify what datatypes you want to end up with - for example should 93530 be text or a number - if it could contain preceding numbers it would have to be text unless you want to convert to a number.

Personally I think you need to go the VBA route, but again code cannot be suggested until you've answered the above and what you plan to do with the data - display in a form or report? insert into a table? what?

[ankur_bhardwaj]

My data looks like this :

Name	Location	Name_Link	Location_Link	Final_Location	Material
C50	2398::3276::3916	GA-4-2	58::52::24	558::352::324::70	93530

and i would like to have below data:

Name	Location	Name_Link	Location_Link	Final_Location	Material
C50	2398	GA-4-2	58	558	93530
C50	3276	GA-4-2	52	352	93530
C50	3916	GA-4-2	24	324	93530
C50		GA-4-2		70	93530

2. yeah length of columns can vary
3. i want to insert this data into a table.

Kindly guide

VBA is used extensively in Access


in that case, it cannot be done in a single query - best you could is to write separate queries for every possible combination of elements for each row - using left, right mid and instr functions

However I can't suggest what this would look like without you clarifying the following:

1. Your presentation of data is not clear as to what heading relates to what element - layout your requirement like this, ideally without spaces in field names and use . or _ rather than spaces - multiple spaces are removed from the text.
Name..LocationName....Link.........Location..LinkF inaLocation..Material
C50....2398.................GA-4-2.....58..........558....................93530

and name is a reserved word, change it to something more meaningful

2. whether the length of elements can vary. e.g. is the first column (C50) always 3 characters and the last column 5 characters or can that vary as well?

3. You also need to clarify what datatypes you want to end up with - for example should 93530 be text or a number - if it could contain preceding numbers it would have to be text unless you want to convert to a number.

Personally I think you need to go the VBA route, but again code cannot be suggested until you've answered the above and what you plan to do with the data - display in a form or report? insert into a table? what?

[orange]

Where does the data originate? Can you change the format? Always easier to make changes as close to source as possible.

[ankur_bhardwaj]

Unfortunately we can't change the format. I know data design is awful.

Where does the data originate? Can you change the format? Always easier to make changes as close to source as possible.

[orange]

Where does the data originate? Can you change the format? Always easier to make changes as close to source as possible.

Here is a routine that may help you get started:

Option Compare Database
Option Explicit

'Name    Location    Name_Link   Location_Link   Final_Location
'C50     2398::3276::3916    GA-4-2  58::52::24  558::352::324::70

'Name and Name_Link are related
'Location and Location_Link are relative values realted to Name
'Final_Location applies to Name,Name_link only

Type mRec
    mName As String
    mLocation As Variant
    mName_link As String
    mLocation_Link As Variant
    mFinal_Location As Variant
End Type

'---------------------------------------------------------------------------------------
' Procedure : concX
' Author    : mellon
' Date      : 12/07/2016
' Purpose   :query to split concatenated string into separate parts
'  https://www.accessforums.net/showthread.php?t=60751&p=324641#post324641
'---------------------------------------------------------------------------------------
'
Sub concX()
          Dim Q As mRec
          Dim i As Integer, j As Integer, k As Integer, m As Integer
          Dim x As String
          Dim xarray As Variant
          Dim LocArray As Variant
          Dim LocLink As Variant
          Dim FinalLoc As Variant
   On Error GoTo concX_Error

10        x = "C50 2398::3276::3916 GA-4-2 58::52::24 558::352::324::70"
20        xarray = Split(x, " ")
30        For i = LBound(xarray) To UBound(xarray)
              'Debug.Print xarray(i)
40            Select Case i
              Case 0
50                Q.mName = xarray(i)
60                Debug.Print Q.mName
                  
70            Case 1
80                Q.mLocation = xarray(i)
90                LocArray = Split(xarray(i), "::")
100               For j = LBound(LocArray) To UBound(LocArray)
110                   Debug.Print "   " & LocArray(j)
120               Next j
                  
130           Case 2
140               Q.mName_link = xarray(i)
150               Debug.Print "    " & Q.mName_link
                  
160           Case 3
170               Q.mLocation_Link = xarray(i)
180               LocLink = Split(xarray(i), "::")
190               For k = LBound(LocLink) To UBound(LocLink)
200                   Debug.Print "     " & LocLink(k)
210               Next k
                  
220           Case 4

230               Q.mFinal_Location = xarray(i)
240               FinalLoc = Split(xarray(i), "::")
250               For m = LBound(FinalLoc) To UBound(FinalLoc) - 1
                      'Debug.Print "       -- " & FinalLoc(m)
260                   Debug.Print Q.mName & " " & LocArray(m) & " " & Q.mName_link & " " & LocLink(m) & " " & FinalLoc(m)
270               Next m
280              Debug.Print Q.mName & " " & Q.mName_link & " " & FinalLoc(UBound(FinalLoc))
290           Case Else
300               MsgBox "an error hs occurred in parsing the incoming string"
310           End Select
320       Next i

   On Error GoTo 0
   Exit Sub

concX_Error:

    MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure concX of Module Module5"

End Sub

Debug output

C50
   2398
   3276
   3916
    GA-4-2
     58
     52
     24
C50 2398 GA-4-2 58 558
C50 3276 GA-4-2 52 352
C50 3916 GA-4-2 24 324
C50 GA-4-2 70