Partial replace on SQL image data column

This question is related to another one I posted earlier.

To recap, I need to fix an issue with an ancient legacy app where people messed up data storage by re-installing the software the wrong way.

The application stores data by saving a record in an SQL DB. Each record holds a reference to a file on disk of which the filename auto-increments.

By re-installing the app the filename auto-increment was re-set so the DB now holds multiple unrelated records which reference the same filename and I have to directories with files which I obviously cannot merge because of these identical filenames. The files hold no reference to the DB data so the only course of action that remains is to filter the DB records on date created and try to rename "EXED" to "IXED" or something like that.

The DB is relatively simple with one table containing a column that holds data of type "Image".

An example content of this image data is as follows:

0x3200001000000000000000200B0000000EFF00000300000031340000000070EC0100002C5000000400000开发者_如何学Python0C90000005D010000040000007955B63F4D01000004000000F879883E4F01000004000000BC95563E98010000040000009A99993F4A01000004000000000000004B01000004000000000000009101000004000000000000004E01000004000000721C83425101000004000000D841493F5E01000004000000898828414101000004000000F2D2BD3F4201000004000000FCA9B13F40010000040000007574204244010000040000000000204345010000040000007DD950414601000004000000000000004701000004000000000000009201000004000000000000008701000004000000D2DF13426A0100000400000000005C42740100000400000046B68F40500100000400000018E97A3F7901000004000000FB50CF3C7A01000004000000E645703F99010000040000000000E0404C010000040000008716593F8601000004000000000006439A0100000400000000008040700100000400000063D887449E01000004000000493CBA3E9C0100000400000069699D429B01000004000000DD60CA3F9D0100000400000035DE3C44B4010000040000008B5C744433000000040000003D0ABB4134000000040000000AFF7C44350000000400000093CB3942750400000400000054A69F41BA010000040000002635C64173040000040000008367C24100000080690100002B5000003101000032000010000000000000002009000000000000000100000000000000F00000000000000080080100000100000010000000540100000100000021F0AA42270000000200000010000000540100000200000021F0AA42280000000300000010000000540100000300000059C9E6432900000004000000100000005401000004000000637888442A00000005000000100000005401000005000000DFEF87442B00000006000000100000005401000006000000000000002C00000007000000100000005401000007000000000000002D00000008000000100000005401000008000000000000002D000000090000001000000054010000090000002F353D442D0000000A00000010000000540100000A00000035DE3C44340000000B00000010000000540100000B0000008B5C7444240000009D50000010000000CDCCCC3E2C513B41F65D5F3F2C51BB419E50000010000000CCBA2C3FE17C8C411553B13F83F32142000000403700000000FE0000090000004558454434386262002D50000008000000447973706E6F65008E5000000E00000056454C442052414D502033363000000000F000000000

The data is apparently Hex which mostly encodes meaningless crap but also holds the name of physical files (towards the end of the data field) in the filesystem that is linked to the SQL records:

??@7???????????EXED48bb?-P??????Dyspnoe??P??????VELD RAMP 360

I'm interested in the EXED part.

There is no clear regularity in the offset at which the filename appears and the filename is of variable length (so I do not know beforehand how long the substring will be).

I can call up all records with SQL like this:

    SELECT COUNT(*) as "Number of EXED Files after critical date"
    FROM [ZAN].[dbo].[zanu]
    WHERE udata is not null 
          and SUBSTRING(udata, 1 , 2147483647) like '%EXED%' 
          and [udatum] > 0 
          and CONVERT(date,[udatum]) > CONVERT(date,'20100629')

What I would like to do now is know how to replace this EXED substring by something else (e.g. IXID).

I'm unfamiliar with SQL and Googling so far has yielded very little information on my options here.

I also have no other info on the original code that generated this data/the data format/encoding/whatever...

It's a mess really.

Any help is welcome!

An update on this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Threading;

namespace ZANLinq
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                DataContext zanDB = new DataContext(@"Data Source=.\,1433;database=ZAN;Integrated Security=true");


                string strSQL = @"SELECT 
                                    Idnr, 
                                    Udatum, 
                                    Uzeit, 
                                    Unr, 
                                    Uart,
                                    Ubediener, 
                                    Uzugriff, 
                                    Ugr, 
                                    Uflags, 
                                    Usize, 
                                    Udata 
                                 FROM Zanu 
                                 WHERE (Udata IS NOT null and SubString(Udata, 1 , 2147483647) LIKE '%EXED%')
                                 AND (Idnr = '           2')";

                var zanQuery = zanDB.ExecuteQuery<Zanu>(strSQL);

                List<Zanu> list = zanQuery.ToList<Zanu>();

                foreach (Zanu zanTofix in list)
                {
                    string strOriginal = ASCIIEncoding.ASCII.GetString(zanTofix.Udata);
                    string strFixed = strOriginal.Replace("EXED", "IXED");
                    zanTofix.Udata = ASCIIEncoding.ASCII.GetBytes(strFixed);
                }

                zanDB.SubmitChanges();

                //Console.WriteLine(zanResults.Count<Zanu>().ToString());

            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }

        }
    }
}

It finds the records I'm interested in, I can easily manipulate the data but the commit doesnt work. I'm stumped, there are no exceptions, no indication the code is wrong.

Anybody have ideas?

UPDATE:

I think the above does not work because my table appears to have a composite PK (I cannot change this):

Since I could not debug this (no info anywhere, no exceptions, just a silent fail of the submitchanges()) I decided to use another approach and abandon Linq2SQL altogether:

try
            {
                SqlConnection thisConnection = new SqlConnection(@"Network Library=DBMSSOCN;Data Source=.\,1433;database=ZAN;Integrated Security=SSPI");

                DataSet zanDataSet = new DataSet();
                SqlDataAdapter zanDa;
                SqlCommandBuilder zanCmdBuilder;

                thisConnection.Open();      

                //Initialize the SqlDataAdapter object by specifying a Select command 
                //that retrieves data from the sample table.
                zanDa = new SqlDataAdapter(@"SELECT 
                                              Idnr, 
                                              Udatum, 
                                              Uzeit, 
                                              Unr, 
                                              Uart,
                                              Ubediener, 
                                              Uzugriff, 
                                              Ugr, 
                                              Uflags, 
                                              Usize, 
                                              Udata 
                                           FROM Zanu 
                                           WHERE (Udata IS NOT null and SubString(Udata, 1 , 2147483647) LIKE '%IXED%')
                                           AND (Idnr = '           2')
                                           AND (Uzeit = '13:21')", thisConnection);

                //Initialize the SqlCommandBuilder object to automatically generate and initialize
                //the UpdateCommand, InsertCommand, and DeleteCommand properties of the SqlDataAdapter.
                zanCmdBuilder = new SqlCommandBuilder(zanDa);

                //Populate the DataSet by running the Fill method of the SqlDataAdapter.
                zanDa.Fill(zanDataSet, "Zanu");

                Console.WriteLine("Records that will be affected: " + zanDataSet.Tables["Zanu"].Rows.Count.ToString());

                foreach (DataRow record in zanDataSet.Tables["Zanu"].Rows)
                {
                    string strOriginal = ASCIIEncoding.ASCII.GetString((byte[])record["Udata"]);
                    string strFixed = strOriginal.Replace("IXED", "EXED");
                    record["Udata"] = ASCIIEncoding.ASCII.GetBytes(strFixed);

                    //string strPostMod = ASCIIEncoding.ASCII.GetString((byte[])record["Udata"]);  
                }

                zanDa.Update(zanDataSet, "Zanu"); 

                thisConnection.Close();

                Console.ReadLine();

            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }

This seems to work but any input on why the Linq does not work and whether or not my second solution is efficient/optimal or not is still very much appreciated.