Split String To List

[paul3252]

I have had a brief look through the forum but I am not sure what functions I need to use to achieve the following, although I am pretty sure it will be something to do with a list and perhaps Regex, although the latter may be overkill.

I have a sqlite database, and one of the fields in the database has a selection of keyword(s), separated by a commas. I want to read through the database and build a listbox of 'unique' keywords to then use as the basis of a search. More than one record for example will contain the same keywords so I don't want to duplicate these.

What function is best used to 'split' the keywords in a record field, so for example 'Apple, Banana, Coconut' becomes

Apple
Banana
Coconut

The next record may have

Apple, Egg Plant so my list would now look like;

Apple
Banana
Coconut
Egg Plant

etc. I will then sort the list alphabetically which is why Lists seems to be the best for this, and push it all into a list box so users can then select the appropriate row/keyword which will then be used in a query.

Apologies if this is a simple. Any pointers appreciated. Thanks for your help.

 

[mc73]

I think you can use regex.split, add items, sort them, and finally delete duplicate entries.

 

[paul3252]

Thanks for the pointers, this is what I came up with which works fine.

Sub build_list
   Dim i, i2 As Int
   
   Dim Cursor1 As Cursor
   Dim txt As String
   Dim key_word() As String
   txt="SELECT keywords FROM qa WHERE keywords<>'' ORDER BY keywords"
   Cursor1=sql1.ExecQuery(txt)
   If Cursor1.RowCount=0 Then
      Msgbox("No Keywords","Error")
   Else
      keywords.Initialize
      For i=0 To Cursor1.RowCount-1
         Cursor1.Position=i
         txt=Cursor1.GetString("keywords")
         key_word=Regex.Split(",",txt)
         
         For i2=0 To key_word.Length-1
            keywords.add(key_word(i2).ToLowerCase)
         Next
      Next   
         
         keywords.sort(True)
         i=0
         Do Until i=keywords.Size-1
         txt=keywords.Get(i)
         If i>0 Then 
            If txt=keywords.Get(i-1) Then
               keywords.RemoveAt(i)
               
            End If
         End If
         Log(i)
         Log(keywords.Size-1)
         i=i+1
         Loop
         
         Log(txt)
         
         
         Msgbox(keywords,"FAQ's")
         
         Cursor1.Position=0
         Cursor1.Close
   End If

End Sub