Base64 in SQLite?

Question

Is there something like

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

in SQLite?

If not, are there other similar features in other SQL-style databases? (MySQL, MSSQL, etc.)

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

Answer 1

System.Data.SQLite supports custom functions in .NET. If you use another shell that does not, change the shell. The latest version is here:

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

He was here, but is now 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 intend to do a lot of searches for this data, especially wildcard searches, then you better cache the data (use a trigger) and put it in the FTS index.