SQL query to extract strings from comma delimited string

Question

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"

The best advice here is to stop storing CSV strings in your tables, especially when you consider each CSV value to be a single point of data. Instead, get each fruit string onto a separate record. Fruits love having some breathing room! — Tim Biegeleisen
– Tim Biegeleisen, Commented Nov 27, 2020 at 9:14
Due to some limitation, we can't create more columns on the tables. So, looking for these manipulation to get data on my excel. — Praveen KUMAR
– Praveen KUMAR, Commented Nov 27, 2020 at 9:22

gotqn · Accepted Answer · 2020-11-27 10:35:19Z

1

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

edited Nov 27, 2020 at 10:35

answered Nov 27, 2020 at 9:20

gotqn

44k47 gold badges167 silver badges256 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Praveen KUMAR Over a year ago

We can't declare variables as there is one field in my database which have thousands of rows. I want to write a query on the basis of field name and then extract.

gotqn Over a year ago

@PraveenKUMAR You can use CTE if you don't want to declare variables. Do you want to show you how?

Gustav · Accepted Answer · 2020-11-27 10:33:02Z

0

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"

answered Nov 27, 2020 at 10:33

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

4 Comments

Praveen KUMAR Over a year ago

When I run this SQL query using vba in a procedure.. it says function doesn't exist. I have also created the tiny helper function outside.

Gustav Over a year ago

If located in a code module, compiled and saved, it should be reachable.

Praveen KUMAR Over a year ago

it says undefined function.. I have saved in the same module

Gustav Over a year ago

The same module as ..? But, try to create a new module and move the function to this.

Collectives™ on Stack Overflow

SQL query to extract strings from comma delimited string

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related