Find an replace Guid to binary equivalent with regular expression

Question

I have a string containing a regular SQL-Statement with Guids. The statement should be converted so I can run it against a local SQLite database which cannot handle Guids as described by .NET-Framework. Therefore I have to convert them to their binary representation, which means that the Guid (in SQL) '00000000-0000-0000-0000-000000000000' will become X'00000000000000000000000000000000' for SQLite or 'FE334797-0A46-468D-91F2-0005F1EC67EC' will become X'974733fe460a8d4691f20005f1ec67ec'.

The method to convert a single Guid is as follows:

private static string GetBinaryGuid(Guid guidToConvert)
{
    var guidBytes = guidToConvert.ToByteArray();
    var guidBinary = new StringBuilder();
    foreach (var guidByte in guidBytes)
    {
        guidBinary.AppendFormat(@"{0}", guidByte.ToString("x2"));
    }
    return guidBinary.ToString();
}

The method to find the real Guid(s) in the query-string is:

resultString = Regex.Replace(subjectString, @"\b[A-F0-9]{8}(?:-[A-F0-9]{4}){3}-[A-F0-9]{12}\b", "'$0'", RegexOptions.IgnoreCase);

My question is how to replace the "real" Guid(s) in the string with their respective binary equivalent?

Edit: Just to clarify. I want to fetch all Guid in the string, pass the found Guids to the method mentioned above and replace it within the string. The result should be the SQL-Query with the binary Guid(s) if found in the string.

Edit2:

SELECT * FROM table WHERE Id = 'FE334797-0A46-468D-91F2-0005F1EC67EC'

should become

SELECT * FROM table WHERE Id = X'974733fe460a8d4691f20005f1ec67ec'

Edit3:

@aelor got me the right direction. For my specific case the solution can be found here.

Is the SQL string completely arbitrary, or will it always be in some known form? — eggyal
No. The query-string could contain any number of Guid(s). Even none — KingKerosin
Why not simply store the GUIDs as strings? Aside from saving a tiny amount of storage, what benefit is there in converting to binary form for storage (presumably only then to convert back to string form again on data retrieval)? If you absolutely must convert the data, why not do so in SQLite itself (e.g. using triggers for writes and views for reads)? — eggyal
@eggyal: The query will first be executed on SQL-Server. If the query is what you expect you will add it to the system which will push it to the clients application which will execute the query there (against SQLite). So this is just for simplicity. Add the statement in SQL and convert it in the back so it can be executed on client side. Triggers would put too much effort on this. It's just a nice-to-have feature — KingKerosin
Why doesn't the app that generates the code for the SQL server generate an appropriate query for SQLite if needed, instead of attempting to do this via string transformation? Or pass off sufficient state to the client for it to so generate the statement? Performing this operation through string manipulation, without parsing the SQL, is just poor programming IMHO. — eggyal

aelor aelor · Accepted Answer · 2014-03-14T09:48:40

i know this is very big but thats what I could come up with:

\b([A-F0-9]{2})([A-F0-9]{2})([A-F0-9]{2})([A-F0-9]{2})-([A-F0-9]{2})([A-F0-9]{2})-([A-F0-9]{2})([A-F0-9]{2})-([A-F0-9]{2})([A-F0-9]{2})-([A-F0-9]{12})\b

use this and you will get your result in matched groups.

The replacement string will look like this :

X'\4\3\2\1\6\5\8\7\9\10\11'

use a \L for making it lowercase.\

Demo here

if you are having trouble like this :

'X'974733FE460A91F20005F1EC67EC''

you can easily remove the leading and trailing ' by using a function

public class Main {   
  /**
   * Remove the leading and trailing quotes from <code>str</code>.
   * E.g. if str is '"one two"', then 'one two' is returned.
   *
   *
   * @return The string without the leading and trailing quotes.
   */
  static String stripLeadingAndTrailingQuotes(String str)
  {
      if (str.startsWith("\'"))
      {
          str = str.substring(1, str.length());
      }
      if (str.endsWith("\'"))
      {
          str = str.substring(0, str.length() - 1);
      }
      return str;
  }

}

Find an replace Guid to binary equivalent with regular expression

1 Answers