Split long CSV string into multiple string variables

Question

I have a long CSV string that can have a maximum length of 44,119 characters. I have a SQL stored procedure that accepts 1 to 6 parameters, each of varchar(8000) that way dividing the long CSV into multiple parameters and passing it to the stored procedure.

My stored procedure works fine, but how can I divide a long CSV into different string variables such that they don't exceed string length of 8000 characters?

For example:

string myLongCSV = "1,2,345,5674,234,22,34..." //a long CSV

I cannot use SubString (0, 8000) as the 8,000th character might be breaking a number in the long CSV and not a comma.

I want to write the code in C# to make it divide all the numbers in the long CSV into different string variables making each variable length not exceeding 8000 characters.

What have you tried so far? Also, might TVPs be a better choice? Passing CSV into SQL is generally not a good choice. — Cory Nelson
– Cory Nelson, Commented Sep 15, 2013 at 4:50

Karl Anderson · Accepted Answer · 2013-09-15 04:57:03Z

1

If you can, modify your SQL stored procedure to use a single parameter of data type VACHAR(MAX), because that stores a maximum of 2,147,483,647 characters.

If you cannot, use String.Split function, like this:

string[] words = myLongCSV.Split(',');
foreach (string word in words)
{
    // some logic here to construct your parameters and check their length.
}

edited Sep 15, 2013 at 4:57

Karl Anderson

34.9k12 gold badges69 silver badges83 bronze badges

answered Sep 15, 2013 at 4:50

MikeLim

1,2291 gold badge10 silver badges11 bronze badges

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

4 Comments

Naupad Doshi Over a year ago

I was using sql stored proc with varchar(max) but when executing it with a long parameter, it used to cut off after 8000 characters.

MikeLim Over a year ago

How are you calling the SP from your SP? ADO.NET? Entity Framework? You may be using the wrong argument data type in your code.

Naupad Doshi Over a year ago

I just tested it using the query editor from SQL Server management studio like Exec MyStoredProc '1,2,3,...' //long csv file. But ultimately I want to call it from my C# code.

MikeLim Over a year ago

Make sure to use VARCHAR(MAX) data type. Also check your SSMS options. If you are doing a SELECT, there is a default cut-off length in the returned results.

Karl Anderson · Accepted Answer · 2013-09-15 05:01:28Z

0

Try this.

The idea is to get the 8,000th character, if it's a number, then decrease index until it's ',' and at that point you can do Substring(), like this:

string s = "123,45636...";      
int index = 7;

while (true)
{
    if (s[index] == ',')
    {
        s = s.Substring(0, index);
        break;
    }
    else
    {
        index--;
    }
}

edited Sep 15, 2013 at 5:01

Karl Anderson

34.9k12 gold badges69 silver badges83 bronze badges

answered Sep 15, 2013 at 4:57

Borna

1,1992 gold badges12 silver badges30 bronze badges

2 Comments

Scott Chamberlain Over a year ago

Good idea, but you probibly want to implment this using index = String.LastIndexOf(',', 8000) so you don't need to loop.

Naupad Doshi Over a year ago

Finally I created a varchar(Max) field in database and passing the entire 44119 characters as a CSV string. When I used to use Split in stored proc to separate the CSV by comma, it was chopping off the data after 8000 characters but using charindex and getting the data one by one worked for me.

Collectives™ on Stack Overflow

Split long CSV string into multiple string variables

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related