Base64 in SQLite?

Question

Is there something like

SELECT * FROM foo WHERE base64(bar) LIKE '%bararar%'

in SQLite?

If not, are there other compareable functions in other SQL-style databases? (MySQL, MSSQL and so on)

I really need to know this because I have a huge database where some base64-encodings of strings contain a specific character which I have to filter out. Hope someone can help me.

Answer 1

System.Data.SQLite supports custom functions in .NET. If you're using a different wrapper that doesn't do this, then change the wrapper. The latest version is here:

http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

It used to be here, but is now a part of the main SQLite project:

http://sqlite.phxsoftware.com/

Example:

[SQLiteFunction(Name = "Base64", Arguments = 1, FuncType = FunctionType.Scalar)]
class Base64SQLite : SQLiteFunction
{
  public override object Invoke(object[] args)
  {
    // assumes args[0] is a string, but you can handle binary data too
    var bytes = Encoding.UTF8.GetBytes(Convert.ToString(args[0]));
    return Convert.ToBase64String(bytes);
  }
}

If you're going to be doing a lot of searches of this data, especially wildcard searches, then you're better off caching the data (use a trigger) and putting it into an FTS index.