SQL SERVER – Split Comma Separated List without Using a Function

Create table “tblTest” in sql server

This is how data looks like in the table

With the above data available, they want it split into individual values. Searching for possible solutions, here is the solution query I came up with:

DECLARE @temp TABLE(               ID INT,               Name Varchar(MAX) ) INSERT INTO @temp (ID, Name) SELECT ID, LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) As Name FROM ( SELECT ID, CAST('<XMLRoot><RowData>' + REPLACE(Name,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x FROM   tblTest) tblTest CROSS APPLY x.nodes('/XMLRoot/RowData')m(n) SELECT * FROM @temp WHERE Name = 'kinjal'

Output looks like below.

Hope this would help you in your project. Let me know if you are able to find a different solution to the same problem. The best way to learn is to learn from each other.

Read More

Auto Generate Paired Number

Read More

Read Excel files data and insert in to SQL Table

Create “Book1.xls” file in that add Header like “ProductID” and “vcSerialNo”

Imports System.IO

Imports System.Data.OleDb

Private Sub GetFile()

 

Dim conString As String = ""

Dim strFileType As String = Path.GetExtension(txtpath.Text).ToLower()

Dim xlspath As String = txtpath.Text

'Connection String to Excel Workbook

If strFileType.Trim() = ".xls" Then

conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & xlspath & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

ElseIf strFileType.Trim() = ".xlsx" Then

conString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1""", xlspath)

End If

Dim oledbConnection As New OleDb.OleDbConnection(conString)

Try

Dim query As String = "SELECT [ProductID],[vcSerialNo] FROM [Sheet1$]"

If oledbConnection.State = ConnectionState.Closed Then

oledbConnection.Open()

End If

Dim cmd As New OleDbCommand(query, oledbConnection)

Dim ds As New DataSet()

Dim adptr As New OleDbDataAdapter(cmd)

adptr.Fill(ds)

DataGridView1.DataSource = ds.Tables(0)

Dim i As Integer

For i = 0 To ds.Tables(0).Rows.Count - 1

Dim ProductID = ds.Tables(0).Rows(i)(0).ToString()

      Dim vcSerialNo = ds.Tables(0).Rows(i)(1).ToString()

Dim sqlConnection As New SqlClient.SqlConnection

      Dim sqlCommand As New SqlClient.SqlCommand()

Try

sqlConnection.ConnectionString = "Data Source=" + txtDBSource.Text + ";Initial Catalog=" + txtDBName.Text + ";User Id=" + txtUserName.Text + ";Password=" + txtPW.Text + ";Integrated Security=False;Connect Timeout=30;"

If sqlConnection.State = ConnectionState.Closed Then

      sqlConnection.Open()

End If

      sqlCommand.Connection = sqlConnection

sqlCommand = New SqlClient.SqlCommand("INSERT INTO tProdSerialNoInventory(lProdId, vcSerialNo, bIsAvailable, dtUpdDT, lUpdUserId) VALUES ('" & ProductID & "','" & vcSerialNo & "', 1, '" & Date.Now() & "', 0)", sqlConnection)

sqlCommand.ExecuteNonQuery()

Catch ex As Exception

Finally

If sqlConnection.State = ConnectionState.Closed Then

            sqlConnection.Close()

End If

End Try

Next

Catch ex As Exception

MessageBox.Show("Reocrds not found")

Finally

oledbConnection.Close()

End Try

End Sub

 

Private Sub btnBrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBrowse.Click

If txtDBSource.Text = "" Or txtDBName.Text = "" Or txtUserName.Text = "" Or txtPW.Text = "" Then

            MessageBox.Show("Check Configuration")

            Return

Else

Dim MyFolderBrowser As New System.Windows.Forms.OpenFileDialog

      MyFolderBrowser.Title = "Select the ExcelSheet"

      MyFolderBrowser.Filter = "All files (*.*)|*.*xls"

      If MyFolderBrowser.ShowDialog = Windows.Forms.DialogResult.OK Then

                txtpath.Text = MyFolderBrowser.FileName

                GetFile()

      End If

End If

End Sub

 

Demo Download

Read More