string.Split ignores Null values between delimiter

问题:

I'm trying to convert some data into sql statements with the use of Streamreader and Streamwriter.

My problem is, when i split lines which in which between 2 delimiters is nothing, not even a space, they get ignored and i get a IndexOutOfRange error

because my temparray only goes till temparray[3] , but it should go to like temparray[6] ..

How can i split and use Null values or replace those null values with a simple char, so that i dont get an IndexOutOfRange error when i want to create my sql statements ?

foreach (string a in values)
{
    int temp = 1;
    String[] temparray = a.Split(';');
    streamWriter.WriteLine("Insert into table Firma values({0},'{1}','{2}')", temp, temparray[1], temparray[4]);
    temp++;
}

回答1:

First of all, this is asking for trouble (SQL injection). You should at the very least escape the values parsed from the string.

And you seem to be mistaken, as String.Split does exactly what you want by default: "x;y;;z".Split(';') returns a four-element array {"x", "y", "", "z"}. You can achieve the described behavior by using StringSplitOptions.RemoveEmptyEntries: "x;y;;z".Split(new[] {';'}, StringSplitOptions.RemoveEmptyEntries) returns a three-element array {"x", "y", "z"}. Which is what you do not want, as you say.

Either way, "Überarbeitung der SAV Seite;b.i.b.;;;;PB;".Split(';') returns a seven-element array here for me, so check your inputs and implementation…

回答2:

If you print out your string, I'm pretty sure it will not be what you expect it to be.

static void Main(string[] args)
{
  var result = "Überarbeitung der SAV Seite;b.i.b.;;;;PB;".Split(';');

  foreach (var part in result)
  {
    Console.WriteLine(" --> " + part);
  }

  Console.ReadLine();
}

This works great. It will not ignore the empty values. It will print

 --> Überarbeitung der SAV Seite
 --> b.i.b.
 -->
 -->
 -->
 --> PB
 -->

including the empty values.

Greetings to bib Paderborn :)

回答3:

If you're using SQL Server, you can return empty strings instead of null by using the ISNULL operator in your query.

For example:

SELECT ISNULL(PR_Arbeitstitel, '') FROM Table

回答4:

Why don't you iterate over your temparray to build up a string of param values.

This is by no means perfect, but should point you in the direction

    foreach (string a in values)
    {
        int temp = 1;
        String[] temparray = a.Split(';');

        var stringBuilder = new StringBuilder();

        foreach (var s in temparray)
            stringBuilder.Append("'" + s + "',");

        var insertStatement = string.Format("Insert into table Firma values({0}, {1})", temp, stringBuilder.ToString().TrimEnd(','));

        temp++;
    }

回答5:

Why would you have 2 delimiters in a row with nothing inbetween them? Do you not control the input?

In that case you could control it by inserting a so-called sentinel value, such as "IGNOREMEPLEASE":

String[] temparray = a.Replace(";;", ";IGNOREMEPLEASE;").Split(';');

Then the rest of your code knows that IGNOREMEPLEASE means there was an empty line, and it should be ignored.

That being said, be very careful about what you send to a database, and scrub incoming data that you use to build SQL statements with, to get rid of anything dangerous.

回答6:

I don't see your issue occurring. The following outputs a string.Empty for string[2] and has all 5 elements

        string[] str = "0,1,,3,4".Split(new char[] { ',' });
        foreach (string s in str)
        {
            Debug.Print(s);
        }

output

0
1

3
4

回答7:

I've tried to reproduce with your example of string "Überarbeitung der SAV Seite;b.i.b.;;;;PB;" but everything was fine. I've got 7 items in array. You can try to use

string s = "Überarbeitung der SAV Seite;b.i.b.;;;;PB;";
var result = s.Split(new[] { ';' }, StringSplitOptions.None);

To be sure that StringSplitOptions.RemoveEmptyEntries is not enabled.

回答8:

Perhaps use the ?? operator:

streamWriter.WriteLine(
    "Insert into table Firma values({0},'{1}','{2}')",
    temp,
    temparray[1] ?? 'x',
    temparray[4] ?? 'x');

This still is only safe, though, if you know for sure that your input has at least 5 tokens after splitting. If you can't guarantee this you'll need to wrap it in a conditional:

if (temparray.Length < 5)
{
    // handle invalid input
}