How to remove a char from a string, based on its codepage index?

Question

I am writing a .NET-software in C# that should transfer data from one database to another. Every character with an index greater than 127 of codepage 1252 causes trouble in the target database, so I want to remove those characters from the values (strings), before I write them into the target database.

I have been searching and trying a lot, but until now I have only found solutions for doing that for ASCII or UTF indexes. I need a solution for indexes of codepage 1252.

Edit: here is my closest approach so far:

protected string GetSqlValue(string input, bool isStringValue = true)
{
    if (string.IsNullOrWhiteSpace(input)) return "''";
    else
    {
        //TODO: remove all characters with an index greater than 127 in codepage 1252.
        Encoding targetEncoding = Encoding.GetEncoding(1252);
        byte[] tmp = targetEncoding.GetBytes(input);
        for (int i=0;i<tmp.Length;i++)
        {
            if (tmp[i] > 127) tmp = tmp.Where((source, index) => index != i).ToArray();
        }
        input = targetEncoding.GetString(tmp);

        if (isStringValue) return "'" + input + "'";
        else return input;
    }
}

Answer 1

Before I show you a solution to your question, I'd suggest, that you have a look at your encodings (in DB and Code) and solve those problems instead of removing chars.

Your solution ignores that a single char can be represented by multiple bytes. Here a unicode example:

Console.WriteLine("a: " + string.Join("-", Encoding.Unicode.GetBytes("a").Select(s => s.ToString("X2"))));
Console.WriteLine("€: " + string.Join("-", Encoding.Unicode.GetBytes("€").Select(s => s.ToString("X2"))));

You have to check every char in your string and choose only those which match your requirement.

static void Main(string[] args)
{
    string str = "abc€def!\"§$%&/()=?`";

    var enc = Encoding.GetEncoding(1252);

    Console.WriteLine("All:     " + str);
    // Select all chars which have a total value below 128
    IEnumerable<char> chars = str.Where(s => ConvertLittleEndian(enc.GetBytes(s + "")) < 128);

    // reassamble string
    Console.WriteLine("Reduced: " + String.Concat(chars));
}

static ulong ConvertLittleEndian(byte[] array)
{
    int pos = 0;
    ulong result = 0;
    foreach (byte by in array)
    {
        result |= ((ulong)by) << pos;
        pos += 8;
    }
    return result;
}

The static method ConvertLittleEndian() is a copy of the first answer from this question: Convert byte array to int

Answer 2

I have to admit that I have been mistaken about the origin of my problem. It turned out that some data also contained several aposthophes. Those destroyed the DDL and DML statements for the target database. I also had to prevent German umlauts from been deleted too.

so the final version of my methods now looks like this:

/// <summary>
/// Gets the SQL value as German characters of codepage 1252.
/// </summary>
/// <param name="input">The string to convert for the target database.</param>
/// <param name="isStringValue">if set to <c>true</c> return encapsulated in single quotation marks.</param>
/// <returns>"''", or the value itself.</returns>
protected string GetSqlValue(string input, bool isStringValue = true)
{
    if (string.IsNullOrWhiteSpace(input)) return "''";
    else
    {
        Encoding targetEncoding = Encoding.GetEncoding(1252);

        // Remove all characters that are not part of codepage 1252.
        input = targetEncoding.GetString(targetEncoding.GetBytes(input));

        // Remove unsupported special characters.
        byte[] tmp = targetEncoding.GetBytes(input);
        for (int i = 0; i < tmp.Length; i++)
        {
            // Don't delete German umlauts.
            if (tmp[i] == 0xc4 /* Ä */ || tmp[i] == 0xe4 /* ä */ || tmp[i] == 0xd6 /* Ö */ || tmp[i] == 0xf6 /* ö */ || tmp[i] == 0xdc /* Ü */ || tmp[i] == 0xfc /* ü */) continue;

            // Delete non German characters and all kind of apostrophes.
            if (tmp[i] >= 0x80 || tmp[i] < 0x20 || tmp[i] == 0x27 || tmp[i] == 0x60) tmp = tmp.Where((source, index) => index != i).ToArray();
        }
        input = targetEncoding.GetString(tmp);

        if (isStringValue) return "'" + input + "'";
        else return input;
    }
}

Thank you very much for your help.

P.S.: I know this question was not as it should have been. I will do better once I get more into how things should be done here.