Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How do you delete duplicate data in a string

Posted on 2011-02-12
9
Medium Priority
?
313 Views
Last Modified: 2012-05-11
Hello,

I have an ACCESS table with duplicate data in a string, for example:

ROW1: BEL,BEL,CAN,CZE,DEU
ROW2: CAN,CZE,DEU,USA,USA
ROW3:BEL,FRA,FRA,CZE,DEU

How do you loop through the table and delete the duplicate countries.

Thanks,

Victor
0
Comment
Question by:vcharles
  • 5
  • 3
9 Comments
 

Author Comment

by:vcharles
ID: 34879684
Hello,

Below is the code I'm using to create the string of countries, is there a way to modify it to avoid duplicate countries? The problem is NSNs are sometimes duplicated which causes multiple countries to be included in the string.

Thanks.

Victor

  Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\aopt2002org.mdb;Persist Security Info=True;Jet OLEDB:Database Password=test"

        Dim objConnection As New OleDb.OleDbConnection(ConnectionString)
        objConnection.Open()

        'Dim da As New OleDb.OleDbDataAdapter("Select NIIN, code3 from IDENTIFICATION_DATA", objConnection)
        Dim da As New OleDb.OleDbDataAdapter("Select NSN, COUNTRYALL from AOP5", objConnection)

        Dim dt1 As New DataTable

        da.Fill(dt1)

        da.Dispose()
        da = Nothing

        'da = New OleDb.OleDbDataAdapter("Select NIIN, RN from REFERENCE_NUMBER_DATA", objConnection)
        da = New OleDb.OleDbDataAdapter("Select NSN, COUNTRY from AOP5A", objConnection)


        Dim dt2 As New DataTable

        da.Fill(dt2)

        Dim dv2 As DataView

        dv2 = dt2.DefaultView

        Dim cmd As OleDbCommand

        Dim SQLStr As String


        For Each dr As DataRow In dt1.Rows

            '.RowFilter = "NIIN='" & dr.Item("NIIN") & "'"
            dv2.RowFilter = "NSN='" & dr.Item("NSN") & "'"

            If dv2.Count > 0 Then


                SQLStr = "Update AOP5 Set COUNTRYALL='"

                For Each drv2 As DataRowView In dv2

                    SQLStr &= drv2.Item("COUNTRY") & ","
                Next
                'strip off trailing comma
                SQLStr = SQLStr.Substring(0, SQLStr.Length - 1)
                'add closing apostrophe
                SQLStr &= "'"
                'add WHERE clause
                SQLStr &= " Where NSN='" & dr.Item("NSN") & "'"
                ' MsgBox(SQLStr)
                cmd = New OleDbCommand(SQLStr, objConnection)
                'MsgBox(SQLStr)
                cmd.ExecuteNonQuery()
                cmd.Dispose()
                cmd = Nothing
            End If
        Next
0
 
LVL 9

Expert Comment

by:sshah254
ID: 34879722
You will not be able to do it in SQL.  You'll have to resort to VBA.

In VBA, get each row (or field) into a string, convert it to an array using the split function, sort the array (this will need to be written as a VBA function since there is no inbuilt sort), then select the unique values and do what you need to do with that ... either store them, or "join" them back into a string and put it back into the table, or print it, or whatever.

Ss
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 34879801
Change this bit

For Each drv2 As DataRowView In dv2

                    SQLStr &= drv2.Item("COUNTRY") & ","
                Next


to


For Each drv2 As DataRowView In dv2
    If SQLStr.Contains(drv2.Item("Country")) = False Then
       SQLStr &= drv2.Item("COUNTRY") & ","
    End If
Next
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:vcharles
ID: 34879924
Thank you for the solution. Is there a way to sort the countries in the string? For example, in a row contains the following the data: BEL,USA,FRA,CAN.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34879947
Yes. Try this

Dim dTable As New DataTable
dTable.Columns.Add("Country", GetType(String))
For Each drv2 As DataRowView In dv2
    dTable.DefaultView.RowFilter = "Country='" & drv2.Item("Country")
    If dTable.DefaultView.Count = 0 Then
       Dim dRow as DataRow = dTable.NewRow()
       drow(0) = drv2.item("Country")
       dTable.Rows.Add(drow)
    End If
    dTable.DefaultView.RowFilter = ""
Next

For i As Integer = 0 to dTable.Rows.Count - 1
     SQLStr &= dTable.Rows(i).Item(0) & ","
Next
0
 

Author Comment

by:vcharles
ID: 34880011
Thanks for the code. I included it at the end of the existing code but I received the following  error on the deign mode: "Variable SQLStr is used before it has been assigned a value" and when I run the project I received the folowing error: "The expression contains an invalid string constant: 'GRC." on line: dTable.DefaultView.RowFilter = "Country='" & drv2.Item("Country").

    Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\aopt2002org.mdb;Persist Security Info=True;Jet OLEDB:Database Password=testaopupdate"

        Dim objConnection As New OleDb.OleDbConnection(ConnectionString)
        objConnection.Open()

        'Dim da As New OleDb.OleDbDataAdapter("Select NIIN, code3 from IDENTIFICATION_DATA", objConnection)
        Dim da As New OleDb.OleDbDataAdapter("Select FIF, COUNTRYFUZEALLA from AOP5", objConnection)

        Dim dt1 As New DataTable

        da.Fill(dt1)

        da.Dispose()
        da = Nothing

        'da = New OleDb.OleDbDataAdapter("Select NIIN, RN from REFERENCE_NUMBER_DATA", objConnection)
        da = New OleDb.OleDbDataAdapter("Select FIF, COUNTRY from AOP5A", objConnection)


        Dim dt2 As New DataTable

        da.Fill(dt2)

        Dim dv2 As DataView

        dv2 = dt2.DefaultView

        Dim cmd As OleDbCommand

        Dim SQLStr As String


        For Each dr As DataRow In dt1.Rows

            '.RowFilter = "NIIN='" & dr.Item("NIIN") & "'"
            dv2.RowFilter = "FIF='" & dr.Item("FIF") & "'"

            If dv2.Count > 0 Then


                SQLStr = "Update AOP5 Set COUNTRYFUZEALLA='"

                ' For Each drv2 As DataRowView In dv2

                'SQLStr &= drv2.Item("COUNTRY") & ","
                'Next
                For Each drv2 As DataRowView In dv2
                    If SQLStr.Contains(drv2.Item("COUNTRY")) = False Then
                        SQLStr &= drv2.Item("COUNTRY") & ","
                    End If
                Next
                'strip off trailing comma
                SQLStr = SQLStr.Substring(0, SQLStr.Length - 1)
                'add closing apostrophe
                SQLStr &= "'"
                'add WHERE clause
                SQLStr &= " Where FIF='" & dr.Item("FIF") & "'"
                ' MsgBox(SQLStr)
                cmd = New OleDbCommand(SQLStr, objConnection)
                'MsgBox(SQLStr)
                cmd.ExecuteNonQuery()
                cmd.Dispose()
                cmd = Nothing
            End If
        Next
        Dim dTable As New DataTable
        dTable.Columns.Add("Country", GetType(String))
        For Each drv2 As DataRowView In dv2
            dTable.DefaultView.RowFilter = "Country='" & drv2.Item("Country")  **Error Line
            If dTable.DefaultView.Count = 0 Then
                Dim dRow As DataRow = dTable.NewRow()
                dRow(0) = drv2.Item("Country")
                dTable.Rows.Add(dRow)
            End If
            dTable.DefaultView.RowFilter = ""
        Next
        For i As Integer = 0 To dTable.Rows.Count - 1
            SQLStr &= dTable.Rows(i).Item(0) & "," ****Error line
        Next
        MsgBox("COMPLETED")
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34880110
Change this

dTable.DefaultView.RowFilter = "Country='" & drv2.Item("Country").


to

dTable.DefaultView.RowFilter = "Country='" & drv2.Item("Country") & "'"


Also, the code I provide needs to replace this code

                For Each drv2 As DataRowView In dv2
                    If SQLStr.Contains(drv2.Item("COUNTRY")) = False Then
                        SQLStr &= drv2.Item("COUNTRY") & ","
                    End If
                Next
0
 

Author Comment

by:vcharles
ID: 34880334
I replaced the code as shown below, but it still doesn't sort to countries.

For Each dr As DataRow In dt1.Rows

            '.RowFilter = "NIIN='" & dr.Item("NIIN") & "'"
            dv2.RowFilter = "FIF='" & dr.Item("FIF") & "'"

            If dv2.Count > 0 Then


                SQLStr = "Update AOP5 Set COUNTRYFUZEALLA='"

                ' For Each drv2 As DataRowView In dv2

                'SQLStr &= drv2.Item("COUNTRY") & ","
                'Next

                'For Each drv2 As DataRowView In dv2
                'If SQLStr.Contains(drv2.Item("COUNTRY")) = False Then
                'SQLStr &= drv2.Item("COUNTRY") & ","
                'End If
                'Next

                For Each drv2 As DataRowView In dv2
                    If SQLStr.Contains(drv2.Item("COUNTRY")) = False Then
                        SQLStr &= drv2.Item("COUNTRY") & ","
                    End If
                Next

                'strip off trailing comma
                SQLStr = SQLStr.Substring(0, SQLStr.Length - 1)
                'add closing apostrophe
                SQLStr &= "'"
                'add WHERE clause
                SQLStr &= " Where FIF='" & dr.Item("FIF") & "'"
                ' MsgBox(SQLStr)
                cmd = New OleDbCommand(SQLStr, objConnection)
                'MsgBox(SQLStr)
                cmd.ExecuteNonQuery()
                cmd.Dispose()
                cmd = Nothing
            End If
        Next
        Dim dTable As New DataTable
        dTable.Columns.Add("Country", GetType(String))
        For Each drv2 As DataRowView In dv2
            dTable.DefaultView.RowFilter = "Country='" & drv2.Item("Country") & "'"

            If dTable.DefaultView.Count = 0 Then
                Dim dRow As DataRow = dTable.NewRow()
                dRow(0) = drv2.Item("Country")
                dTable.Rows.Add(dRow)
            End If
            dTable.DefaultView.RowFilter = ""
        Next
        For i As Integer = 0 To dTable.Rows.Count - 1
            SQLStr &= dTable.Rows(i).Item(0) & "," '****Error line
        Next

Thanks.
0
 

Author Closing Comment

by:vcharles
ID: 34882624
Thank You.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question