parsing out a string using a special character

[joe789]

Hi Folks,

Any help would be greatly appreciated.

Example of data in a single field:

CLP*102861#3299003*1*42.66*42.66*0*MC*000000141450*53

Is there a way to make a query so that everything between the * and next * gets into its own field; for example:

Field1: CLP
Field2: 102861#3299003
Field3: 1
Field4: 42.66
Field5: 42.66
Field6: 0
Field7: MC
Field8: 000000141450
Field9: 53

This string referred to above is already in a table and not a flat file, I know if it was in a flat file it would be easy to just create a import script using '*' as the deliminator, but I cannot use that method for this as it is way too complex ... is there an easy way to do this with a query, keeping in mind that not each * is equal distance apart from the next * in all the rows or else I would just use a mid statement ...

Thank you very much,

Joe

 

[philben]

Hello,

One solution is :

• Add this function in a VBA module :

Public Function GetField(ByVal Data As Variant, ByVal FieldNumber As Integer) As String
    On Error Resume Next
    GetField = Split(Data, "*")(FieldNumber - 1)
End Function

• The query :

SELECT 
  GetField([data],1) AS Field1, 
  GetField([data],2) AS Field2, 
  GetField([data],3) AS Field3, 
  GetField([data],4) AS Field4, 
  GetField([data],5) AS Field5, 
  GetField([data],6) AS Field6, 
  GetField([data],7) AS Field7, 
  GetField([data],8) AS Field8, 
  GetField([data],9) AS Field9
FROM MyTable;

Best regards,

Philippe

 

[Khalid_Afridi]

Another solution is:
get all the data to any excel file and apply * delimitation in the excel sheet to break each row in the columns , after that import the file to your access database with the clear data.