Results 1 to 11 of 11

Thread: SQL – Join, manipulate and comma delimit column

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    500

    SQL – Join, manipulate and comma delimit column

    I have a SQL table (TableA) where one of its column contains some very dirty data where I am interested in. The data in that column looks like this in one example (please also note the “&” sign):

    Code:
     053-10003 - North America, 1459 - Research & Development, 225 - PS, Quality & IT, 322-1366 - Biopharm, 3522 - People & Organisation, 416-100 - International Operations, 1964 - DMS Business Support, 1966 - Device Metrology, 4715 - Commercial Strategy & Corporate
    What I need from this string is
    1- numbers before “ - ” for every set of string e.g.:
    Code:
     053-10003, 1459, 322-1366, 3522, 416-100, 1964, 1966, 4715 4715
    2- And if there is no “ - ” then the whole set e.g.:
    Code:
     Quality & IT
    3- Here, I am only interested in the numbers in step 1 if the numbers match with another “tableB” column X
    The numbers in TableB column X are as follows:
    Code:
     043
    043.01
    043.02
    043.03
    043.04
    043.05
    043.06
    043.99
    049
    049.01
    049.02
    049.03
    049.04
    049.05
    049.06
    049.09
    196
    1964
    1964.01
    1964.02
    1964.04
    1965
    1966
    1966.01
    1966.02
    1966.03
    1966.04
    1966.05
    1968
    198
    198.01
    198.02
    198.04
    198.05
    198.06
    2737
    2737.02
    2737.04
    2782
    2782.01
    2782.02
    2782.04
    2782.05
    2782.06
    908
    So the result for this example should look this:

    ColumnA ColumnB
    Wewe 1964, 1966

    ColumnA is another thing in table A that I need but there is no manipulation required for them.

    Sample data can be provided.

    Thanks in advance.

  2. #2
    Frenzied Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    1,060

    Re: SQL – Join, manipulate and comma delimit column

    I would write a program that would go through each row. I'd split using the commas into a List of String and use RegEx to detect and extract your numbers. If there's no RegEx match, use the item string. Arrange it however you want in a DataTable. When done, send the DataTable to the Db using SqlBulkCopy.

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    500

    Re: SQL – Join, manipulate and comma delimit column

    Believe or not, that is what I have done
    But it must be some SQL thing.

    Code:
        Sub UpdateSubTablesWithImpactedTeams()
    
            Dim Files() As String = GetTheFileNames()
            Dim MySQLTable As String = ""
            Dim MyQuery As String = ""
            Dim QueryWithRollback As String = ""
            Dim ImpactedTeamsString As String = ""
            Dim ImpactedOrgArray() As String = Nothing
            Dim FullTable As System.Data.DataTable = Nothing
            Dim DMS_areas As System.Data.DataTable = GetMyDataTable("Select Distinct Area from phonebookDMS order by area asc")
            'Dim TeamTable As System.Data.DataTable = Nothing
    
            Using WC As New WaitCursor
                For Each File As String In Files
    
                    FullTable = GetMyDataTable("Select *  FROM  " & "QDOC_NTO_" & File)
    
                    If FullTable.Rows.Count > 0 Then
    
                        Dim TeamTable As New System.Data.DataTable
    
                        With TeamTable
    
                            .TableName = "QDOC_NTO_" & File
                            .Columns.Add("ID", GetType(String))
                            .Columns.Add("Status", GetType(String))
                            .Columns.Add("Primary process", GetType(String))
                            .Columns.Add("Document name", GetType(String))
                            .Columns.Add("Doc Nr.", GetType(String))
                            .Columns.Add("Ver.", GetType(String))
                            .Columns.Add("Cont. Level", GetType(String))
                            .Columns.Add("Sub type", GetType(String))
                            .Columns.Add("ISOtrain Module", GetType(String))
                            .Columns.Add("Prop. Eff. Date", GetType(String))
                            .Columns.Add("Impacted Orgs.", GetType(String))
                            .Columns.Add("Comments", GetType(String))
                        End With
    
                        Dim RowCounter As Integer = 0
                        Dim ExtractedTeamNum As String = ""
    
                        For Each Drow As DataRow In FullTable.Rows
                            ImpactedTeamsString = ""
                            ExtractedTeamNum = ""
                            RowCounter = 0
                            ImpactedOrgArray = (Drow("Impacted Orgs.").ToString).Split(",")
                            If ImpactedOrgArray.Length > 0 Then
                                For Each Value As String In ImpactedOrgArray
                                    'If Value.Contains(" - ") Then
                                    ExtractedTeamNum = Trim(Value).Split(" - ")(0)
                                    For Each AreaRow As DataRow In DMS_areas.Rows
                                        If AreaRow("Area").ToString = ExtractedTeamNum Then
                                            If Not ImpactedTeamsString.Contains(ExtractedTeamNum) Then ' only unique
                                                ImpactedTeamsString = ImpactedTeamsString & ExtractedTeamNum & ","
                                            Else
                                                ImpactedTeamsString = ImpactedOrgArray(0)
                                            End If
                                            RowCounter = RowCounter + 1
                                        End If
                                    Next AreaRow                             ' End If
                                Next Value
                            End If
                            If RowCounter > 0 Then
                                ImpactedTeamsString = ImpactedTeamsString.Trim(",")
                            Else
                                ImpactedTeamsString = "Corporate"
                            End If
    
                            Dim drNewRow As DataRow = TeamTable.NewRow
                            drNewRow("ID") = Drow("ID").ToString
                            drNewRow("Status") = Drow("Status").ToString
                            drNewRow("Primary process") = Drow("Primary process").ToString
                            drNewRow("Document name") = Drow("Document name").ToString
                            drNewRow("Doc Nr.") = Drow("Doc Nr.").ToString
                            drNewRow("Ver.") = Drow("Ver.").ToString
                            drNewRow("Cont. Level") = Drow("Cont. Level").ToString
                            drNewRow("Sub type") = Drow("Sub type").ToString
                            drNewRow("ISOtrain Module") = Drow("ISOtrain Module").ToString
                            drNewRow("Prop. Eff. Date") = Drow("Prop. Eff. Date").ToString
                            drNewRow("Comments") = Drow("Comments").ToString
                            drNewRow("Impacted Orgs.") = ImpactedTeamsString
                            TeamTable.Rows.Add(drNewRow)
                        Next Drow
    
                        If TeamTable.Rows.Count > 0 Then
    
                            Call DeleteAllRecordsFromTable("QDOC_NTO_" & File)
    
                            Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLcon)
                                bulkCopy.DestinationTableName = "QDOC_NTO_" & File ' "JFIM_Module_Detail_test"
                                ' Set the timeout.
                                bulkCopy.BulkCopyTimeout = 160
    
                                With bulkCopy.ColumnMappings
                                    'source column name, destination column name
                                    .Add("ID", "ID")
                                    .Add("Status", "Status")
                                    .Add("Primary process", "Primary process")
                                    .Add("Document name", "Document name")
                                    .Add("Doc Nr.", "Doc Nr.")
                                    .Add("Ver.", "Ver.")
                                    .Add("Cont. Level", "Cont. Level")
                                    .Add("Sub type", "Sub type")
                                    .Add("ISOtrain Module", "ISOtrain Module")
                                    .Add("Prop. Eff. Date", "Prop. Eff. Date")
                                    .Add("Comments", "Comments")
                                    .Add("Impacted Orgs.", "Impacted Orgs.")
                                End With
    
                                Try
                                    SQLcon.Open()
                                    ' Write from the source table to the destination table (server).
                                    bulkCopy.WriteToServer(TeamTable)
                                    SQLcon.Close()
                                Catch ex As SqlException
                                    SQLcon.Close()
                                    MsgBox(ex.Message, MsgBoxStyle.Critical, "Error happened during bulk copy to table: " & MySQLTable)
    
                                Catch ex As Exception
                                    SQLcon.Close()
                                    MsgBox(ex.Message, MsgBoxStyle.Critical, "General error during bulk copy to table: " & MySQLTable)
    
                                End Try
    
    
    
                            End Using
                        End If
    
                    End If
    
                Next
            End Using
        End Sub

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,652

    Re: SQL – Join, manipulate and comma delimit column

    If it's SQL-Server maybe this will get you started:
    http://blogs.lessthandot.com/index.p...ith-sql-server
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  5. #5
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    107,648

    Re: SQL – Join, manipulate and comma delimit column

    I think that one of the difficulties here is that you want to include the dashes that are within some of the numeric text but not the others. You can't use regex for just digits but you can't simply include dashes either. I'm very poor when it comes to regex so maybe there's relatively simple way around that. If it was me, I'd go with the application option for the processing of the data, rather than doing in SQL. In that case, the VB code to process that data is fairly simple:
    vb.net Code:
    1. Dim text = "053-10003 - North America, 1459 - Research & Development, 225 - PS, Quality & IT, 322-1366 - Biopharm, 3522 - People & Organisation, 416-100 - International Operations, 1964 - DMS Business Support, 1966 - Device Metrology, 4715 - Commercial Strategy & Corporate"
    2. Dim parts = text.Split(","c)
    3.  
    4. parts = parts.Select(Function(s) s.Split({" - "}, StringSplitOptions.None).First().Trim()).ToArray()
    5. text = String.Join(", ", parts)
    6.  
    7. Console.WriteLine(text)
    Just noticed that it specifically says "SQL" in the thread title, so maybe a separate app for processing is not acceptable.

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,652

    Re: SQL – Join, manipulate and comma delimit column

    for SQL-Server another option might be the TRANSLATE-Function, excluding digits and dash to translate, on the result of that use REPLACE to replace them with "", then you would only have your leading numbers (incl. optional dash between numbers) with a trailing dash you could easily filter out with a combination of CASE WHEN, RIGHT, LENGTH and/or SUBSTRING Another idea might be to use STRING_SPLIT on the BLANK, your result would always be the first member, or in case of Quality & IT it would be no result.

    EDIT: This is of course all AFTER using STRING_SPLIT on the comma
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  7. #7
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,824

    Re: SQL – Join, manipulate and comma delimit column

    I gave it a go with regex,
    the first one with the Numbers looks ok, the second one still need's some work

    Code:
    Imports System.Text.RegularExpressions
    
    Public Class Form1
    
        Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            Const s1 As String = " 053-10003 - North America, 1459 - Research & Development, 225 - PS, Quality & IT, 322-1366 - Biopharm, 3522 - People & Organisation, 416-100 - International Operations, 1964 - DMS Business Support, 1966 - Device Metrology, 4715 - Commercial Strategy & Corporate"
            Dim mRegex As New Regex("\d+(?:[-]\d+)*")
    
            For Each Mt As Match In mRegex.Matches(s1)
                Debug.Print(Mt.Groups(0).Value)
    
                'debug output:
                '053-10003
                '1459
                '225
                '322-1366
                '3522
                '416-100
                '1964
                '1966
                '4715
            Next
        End Sub
    
        Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
            Const s2 As String = " 053-10003 - North America, 1459 - Research & Development, 225 - PS, Quality & IT, 322-1366 - Biopharm, 3522 - People & Organisation, 416-100 - International Operations, 1964 - DMS Business Support, 1966 - Device Metrology, 4715 - Commercial Strategy & Corporate"
            Dim mRegex As New Regex("(\w+)\s+(&)+\s(\w+)")
    
            For Each Mt As Match In mRegex.Matches(s2)
                Debug.Print(Mt.Groups(0).Value)
    
                'debug output:
                'Research & Development
                'Quality & IT
                'People & Organisation
                'Strategy & Corporate
            Next
        End Sub
    End Class
    hth
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  8. #8
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,652

    Re: SQL – Join, manipulate and comma delimit column

    Nice Chris, and then considering, that the Argument of the PATINDEX-Function looks like Regex......
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  9. #9
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,824

    Re: SQL – Join, manipulate and comma delimit column

    Quote Originally Posted by Zvoni View Post
    Nice Chris, and then considering, that the Argument of the PATINDEX-Function looks like Regex......
    Hi Zvoni,

    looks like Grand has allready solved the Problem, well he's got enough advice to solve it now
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  10. #10

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    500

    Re: SQL – Join, manipulate and comma delimit column

    Hi
    I am trying your different suggestions.
    I was not sure that doing the calculation/manipulation on the client side is the way of doing it. Now, hearing it form the experts I am more confident.

    Thanks again.

  11. #11
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,794

    Re: SQL – Join, manipulate and comma delimit column

    Using the split string here: http://www.vbforums.com/showthread.p...ion-(no-loops)

    Code:
    DECLARE @my_str VARCHAR(MAX)
    
    SET @my_str = '053-10003 - North America, 1459 - Research & Development, 225 - PS, Quality & IT, 322-1366 - Biopharm, 3522 - People & Organisation, 416-100 - International Operations, 1964 - DMS Business Support, 1966 - Device Metrology, 4715 - Commercial Strategy & Corporate'
    
    
    ; WITH aa AS (
    	SELECT s.*
    		, LEN(ItemData) - CHARINDEX('-', REVERSE(s.ItemData)) AS DashRight
    	FROM master.dbo.fn_SplitStringToTable(@my_str, ', ') AS s
    )
    SELECT *
    	, CASE WHEN DashRight = LEN(ItemData)
    		THEN ''
    		ELSE TRIM(LEFT(ItemData, DashRight))
    		END AS Left_Side
    
    	, CASE WHEN DashRight = LEN(ItemData)
    		THEN ItemData
    		ELSE TRIM(SUBSTRING(ItemData, DashRight + 2, 1000))
    		END AS Right_Side
    FROM aa
    For #3, just do a join with your other table and "Left_Side" field

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width