CODEWITHSUNDEEP
sqlvbams-access
Praveen KUMAR
Praveen KUMAR

Reputation: 67

SQL query to extract strings from comma delimited string

I want to extract data from MS Access database to MS Excel using VBA.

I know the code to extract but facing issues manipulating the column.

I want to extract string before first comma, and then string after second and before third comma, and then string after third comma.

As an example. Let's say I have one column called fruits and it has data as

Banana,Apple,Orange,Grapes

I want to create four columns which will have Banana on first, Apple on second, Orange on third and Grapes on fourth column.

Below is what I tried for first column.

SQL = "SELECT SUBSTRING(Fruits,0,CHARINDEX(',',Fruits) as column1 from tablename"

Upvotes: 0

Views: 1244

Answers (2)

gotqn
gotqn

Reputation: 43636

Try this:

DECLARE @Data NVARCHAR(MAX) = 'Banana,Apple,Orange,Grapes'

DECLARE @DataXML XML = '<a>' + REPLACE('Banana,Apple,Orange,Grapes', ',', '</a><a>') + '</a>'

SELECT *
FROM
(
    SELECT T.c.value('.','varchar(255)')
          ,ROW_NUMBER() OVER (ORDER BY T.c)
    FROM @DataXML.nodes('/a') T(c)
) DS ([value], [col])
PIVOT
(
    MAX([value]) FOR [col] IN ([1], [2], [3], [4])
) PVT

SELECT 'Banana,Apple,Orange,Grapes'
 AS [my_column]
INTO [my_table]


WITH DataSource (DataXML) AS
(
    SELECT CAST('<a>' + REPLACE([my_column], ',', '</a><a>') + '</a>' AS XML)
    FROM [my_table] 
)

SELECT *
FROM
(
    SELECT T.c.value('.','varchar(255)')
          ,ROW_NUMBER() OVER (ORDER BY T.c)
    FROM DataSource
    CROSS APPLY [DataXML].nodes('/a') T(c)
) DS ([value], [col])
PIVOT
(
    MAX([value]) FOR [col] IN ([1], [2], [3], [4])
) PVT

Upvotes: 1

Gustav
Gustav

Reputation: 55816

You can create a tiny helper function and use Split:

Public Function GetField( _
    ByVal AllFields As String, _
    ByVal Index As Integer) _
    As String
    
    GetField = Split(AllFields, ",")(Index)
    
End Function

and then:

SQL = "Select GetField([Fruit], 0) As Fruit From YourTable"

Upvotes: 0

Related Questions