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, Mar 14 '14 at 09:49
No. The query-string could contain any number of Guid(s). Even none — KingKerosin, Mar 14 '14 at 09:53
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, Mar 14 '14 at 10:00
@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, Mar 14 '14 at 10:29
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, Mar 14 '14 at 14:55
And in any event, I don't think you've explained why the SQLite database cannot store the field as a text string per my suggestion above. — eggyal, Mar 14 '14 at 14:56

aelor · Accepted Answer · 2014-03-14T12:40:26.920

1

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;
  }

}

edited Mar 14 '14 at 12:40

answered Mar 14 '14 at 09:48

aelor

10,892
3
32
48

Awesome. This is nearly what I want to have. But I do also need the single Quotas to be replaced: 'Guid-Value' -> X'Binary-Value'. How could this be added? – KingKerosin Mar 14 '14 at 10:05
can you please elaborate I dont get it, which quotes you are talking about – aelor Mar 14 '14 at 10:07
[SQL is not a regular language](http://stackoverflow.com/a/5639859), so one cannot solve this problem with regex alone. – eggyal Mar 14 '14 at 10:10
@eggyal you are right, but here look at it as a simple string which has to be replaced. – aelor Mar 14 '14 at 10:14
@aelor I mean the single quotation mark ' should also be in the replacement. Because if I replace '00000000-0000-0000-0000-000000000000' it will become 'X'00000000000000000000000000000000'' instead of X'00000000000000000000000000000000' only. – KingKerosin Mar 14 '14 at 10:22
@KingKerosin oo thats simple , just add a single quote in the replacement string, X'\4\3\2\1\6\5\9\10\11' hope that helps. I will edit it. – aelor Mar 14 '14 at 10:32
@aelor: Sorry, but eventually I am too dumb. But still SELECT * FROM table WHERE Id = 'FE334797-0A46-468D-91F2-0005F1EC67EC' will become SELECT * FROM table WHERE Id = 'X'974733FE460A91F20005F1EC67EC''. See the single quoatation marks around X'...'? – KingKerosin Mar 14 '14 at 10:56
actually the resulting string is X'974733FE460A91F20005F1EC67EC' , okay now for showing it as a string one single quote is added on both the sides, thats the thing – aelor Mar 14 '14 at 10:58
Or you can do one thing , give me one example of sql query that works in your system, i will modify the text accordingly, does SELECT * FROM table WHERE Id = 'X'974733FE460A91F20005F1EC67EC'' or SELECT * FROM table WHERE Id = 'X\'974733FE460A91F20005F1EC67EC\'' work ? – aelor Mar 14 '14 at 11:01
It should only result in SELECT * FROM table WHERE Id = X'974733FE460A91F20005F1EC67EC' with the single quot marks around the binary value but no single quote before the X and after the binary. Hope you know what I mean. – KingKerosin Mar 14 '14 at 11:06
check my update, thats all I could come up with , phew – aelor Mar 14 '14 at 11:14
@aelor The replacement string was missing part 7 and 8. It should be X'/4/3/2/1/6/5/8/7/9/10/11'. But at least, this was the solution I was looking for. Thanks very much – KingKerosin Mar 14 '14 at 12:37
@KingKerosin OOHH i didnt notice it, thanks, i have updated the answer – aelor Mar 14 '14 at 12:41
My point is, performing such a pattern match on an SQL string could match non-GUID expressions (for example, an arithmetic expression involving five columns that happen to be so named). Sure, it's unlikely to ever occur - but relying on that is IMHO poor programming. – eggyal Mar 14 '14 at 14:54

Find an replace Guid to binary equivalent with regular expression

1 Answers1