c# sqlite 巧用正则表达式实现特殊排序

之前发布了一篇c#使用sqlite时正则表达式函数REGEXP如何使用的文章，今天摸索出了用正则表达式实现各种自己想要的排序，注册REGEXP函数时改用Regex.Match函数，直接上代码：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;

namespace SQLlite
{
    class SQLiteDB
    {
        SQLiteConnection sdbConn = new SQLiteConnection();

        public void Program()
        {
            createNewDatabase();
            connectToDatabase();
            createTable();
            fillTable();
            printHighscores();
        }

        //创建一个空的数据库
        void createNewDatabase()
        {
            SQLiteConnection.CreateFile("MyDatabase.sqlite");
        }

        //创建一个连接到指定数据库
        void connectToDatabase()
        {
            sdbConn = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
            sdbConn.Open();
        }

        //在指定数据库中创建一个table
        void createTable()
        {
            string sql = "create table highscores (name varchar(20), score int)";
            SQLiteCommand command = new SQLiteCommand(sql, sdbConn);
            command.ExecuteNonQuery();
        }

        //插入一些数据
        void fillTable()
        {
            IDbTransaction trans = sdbConn.BeginTransaction();
            try
            {
                string sql = "insert into highscores (name, score) values ('A1', 3000)";
                SQLiteCommand command = new SQLiteCommand(sql, sdbConn);
                command.ExecuteNonQuery();

                 sql = "insert into highscores (name, score) values ('A2', 3000)";
                 command = new SQLiteCommand(sql, sdbConn);
                command.ExecuteNonQuery();

                 sql = "insert into highscores (name, score) values ('A11', 3000)";
                 command = new SQLiteCommand(sql, sdbConn);
                command.ExecuteNonQuery();

                 sql = "insert into highscores (name, score) values ('AA1', 3000)";
                 command = new SQLiteCommand(sql, sdbConn);
                command.ExecuteNonQuery();

                 sql = "insert into highscores (name, score) values ('B1', 3000)";
                 command = new SQLiteCommand(sql, sdbConn);
                command.ExecuteNonQuery();

                 sql = "insert into highscores (name, score) values ('2', 3000)";
                 command = new SQLiteCommand(sql, sdbConn);
                command.ExecuteNonQuery();

                 sql = "insert into highscores (name, score) values ('11', 3000)";
                 command = new SQLiteCommand(sql, sdbConn);
                command.ExecuteNonQuery();
                
                trans.Commit();//提交事务
            }
            catch (Exception e)
            {                
                trans.Rollback();//回滚事务
            }
        }

        //使用sql查询语句，并显示结果
        void printHighscores()
        {
            string sql = "select * from highscores WHERE LENGTH(name REGEXP '[A-Z]+') > 0 ORDER BY LENGTH (name REGEXP '[A-Z]+'), name REGEXP '[A-Z]+', LENGTH(name REGEXP '[0-9]+'), name REGEXP '[0-9]+' ";
                        
            SQLiteCommand command = new SQLiteCommand(sql, sdbConn);
            SQLiteDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine(reader.GetString(0));
            }
            Console.WriteLine("OK");
            Console.ReadLine();
        }

        [SQLiteFunction(Name="REGEXP",Arguments=2,FuncType=FunctionType.Scalar)]
        public class REGEXP : SQLiteFunction
        {
            public override object Invoke(object[] args)
            {
                //return System.Text.RegularExpressions.Regex.IsMatch(Convert.ToString(args[1]), Convert.ToString(args[0]));
                return System.Text.RegularExpressions.Regex.Match(Convert.ToString(args[1]), Convert.ToString(args[0])).ToString();
            }
        }        
    }
}