how to split the data in multiple columns?

[casiansala]

Hi,
I am new to SQL, so therefore I have many questions; I have a field containing different properties of the records, like date, author, etc. The different properties are separated by ";" my questions is how to obtain separate columns for each separate property listed?

 

[barny2006]

look at the different functions of string manipulation. you can locate the position of ";" and separate the items. or you may look into using vb script to split the data in a field. to do that, you have to read the rows in the table as a record set, split the field, and create a column for each item in the field separated by ";

 

[dhookom]

I did a quick search of google on VBA Parse Instr. I found and modified this code from MS site. You would call the GetCSWord() function with your field and the position of the string you want to extract. You can optionally provide the delimiter like:
GetCSWord([YourField],2,";")

Function CountCSWords(strText As String, Optional strDelim As String = ",") As Integer
' Counts the words in a string that are separated by strDelim

Dim WC As Integer, Pos As Integer
   If Len(strText) = 0 Then
     CountCSWords = 0
     Exit Function
   End If
   WC = 1
   Pos = InStr(strText, strDelim)
   Do While Pos > 0
     WC = WC + 1
     Pos = InStr(Pos + 1, strText, strDelim)
   Loop
   CountCSWords = WC
End Function

Function GetCSWord(strText As String, Indx As Integer, Optional strDelim As String = ",") As String
' Returns the nth word in a specific field.

Dim WC As Integer, Count As Integer, SPos As Integer, EPos As Integer
   WC = CountCSWords(strText)
   If Indx < 1 Or Indx > WC Then
     GetCSWord = Null
     Exit Function
   End If
   Count = 1
   SPos = 1
   For Count = 2 To Indx
     SPos = InStr(SPos, strText, strDelim) + 1
   Next Count
   EPos = InStr(SPos, strText, strDelim) - 1
   If EPos <= 0 Then EPos = Len(strText)
   GetCSWord = Trim(Mid(strText, SPos, EPos - SPos + 1))
End Function

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]