SQLite3文本与正则表达式

本文介绍SQLite3数据库的文本操作，以及如何使用regexp运算符判断文本内容是否匹配正则表达式的模式。

文本操作

首先回顾一些文本的基本操作和函数。

||运算符会以文本形式连接两侧的数据，并返回连接后的文本内容，如果有一个运算数为null则连接结果为null。下面的代码演示了||运算符的应用。

select 'abc'||null, 'abc' || 123;

执行代码会返回null和'abc123'。

文本内容是否的相同判断，使用=或==运算符，默认会区分字母大小写，如下面的代码。

select 'abc'='abc', 'abc'='ABC';

执行代码会返回1（true）和0（false）。

比较时需要忽略字母大小写时，可以在相等运算符前添加collate nocase关键字，如下面的代码。

select 'abc' collate nocase = 'ABC';

执行代码会返回1（true）。

like运算符可对文本内容进行模糊匹配，其中，百分号（%）可以表示0或多个字符，下画线（_）表示1个字符。下面的代码演示了like运算符的应用。

select 'abc' like 'A%';

执行代码会返回1（true）。查询判断'abc'是否以'A'开始，可以看到，like运算符忽略了字母大小写，如果需要严格判断字母大小写，应使用glob运算符，如下面的代码会返回0（false）。

select 'abc' glob 'A%';

此外，like运算符还有对应的like(pattern,string)函数可以使用，而glob运算符同样有对应的glob(pattern,string)函数可以使用；参数中，pattern指定匹配模式，string指定判断的文本内容。下面的代码演示了这两个函数的应用。

select like('A%','abc'), glob('A%','abc');

执行代码会返回1和0。

length(data)函数可以返回文本中的字符数量或BLOB的字节数。参数为null值时返回null。如length('abc测试')返回5。

lower(text)函数可以将参数text中的字母转换为小写形式，如lower('ABC123')返回'abc123'。参数为null值时返回null。

upper(text)函数可以将参数text中的字母转换为大写形式，如upper('abc123')返回'ABC123'。参数为null值时返回null。

trim()函数包括两个版本，即trim(text)和trim(tet,extra)。函数的功能是删除文本开始位置和结束位置的空格符和指定的内容。默认删除空格符，删除其它字符需要使用参数extra指定。两个相关的函数为ltrim()和rtrim()，其参数设置与trim()函数相同，ltrim()函数用于删除文本开始位置的空格符和指定字符，rtrim()函数用于删除文本结束位置的空格符和指定字符。下面的代码演示了trim()函数的应用。

select trim(' abc '), trim('abc ','a');

执行代码会返回'abc'和'bc'。其中，第一次调用trim()只删除文本开始和结束位置的空格，第二次调用除了空格，还会删除开始位置和结束位置的字母a。此外，如果参数中包含null，则函数返回结果为null。

quote(value)函数，当value为null时返回null，value为数值时原样返回，value是文本内容时会添加单引号，文本中的一个单引号会转换为两个单引号；函数对数据进行转换的主要是防止SQL注入，转换后的内容可以用于SQL语句的拼接。下面的代码演示了quote()函数的应用。

select quote("abc'123");

执行代码会返回'abc''123'。

replace(value,search,replacement)函数的功能是在value中将search替换为replacement的内容。如果一个参数为null则返回null。下面的代码演示了相关应用。

select replace('abc123abc', 'abc', '***');

执行代码会返回***123***。

substr(string,index,count)函数的功能是从string中的第index个字符截取count个字符，如果不指定count则从index位置开始截取所有字符。参数中有一个是null时函数返回null。下面的代码演示了相关应用。

select substr('abcdefg',3,2), substr('abcdefg',3);

执行代码会返回cd和cdefg。

正则表达式

SQLite3数据库中，可以使用regexp运算符判断文本内容是否匹配正则表达式的模式，应用格式为“<文本> regexp <模式>”，其中，<文本>和<模式>都是单引号定义的文本。对应的函数是regexp(pattern,string)，其中，参数pattern指定模式，参数string指定判断的文本内容。下面结合模式的定义来了解regexp运算符的应用，包括匹配内容、匹配次数和匹配位置等。

匹配内容

在模式中定义匹配的内容时，主要包括以下方法：

• 圆点（.），匹配换行符（\n）以外的任意一个字符。
• 匹配正则表达式元字符和特殊字符需要使用\符号转义，匹配其它字符直接书写即可。如匹配\符号需要使用\\来匹配，匹配圆点（.）需要使用\.来匹配等。
• [<字符集>]，匹配<字符集>中的任意一个字符。<字符集>中可以是字符的列表，如"yn"表示可以匹配y或n；也可以使用连接号指定范围，如a-z、0-9、A-Z等。比如，匹配一位十六进制数可以使用[a-fA-F0-9]，即包含大小写的字母a到f和数字0到9。
• [^<字符集>]，匹配<字符集>以外的任意一个字符。如[^abc]会匹配一个abc之外的字符，即不包含a、b、c这三个字符。
• \d，任意一个数字（0到9）字符。
• \D，任意一个非数字字符。与\d匹配的内容相反。
• \s，任意一个空白字符，包括空格、换行符（\n）、回车符（\r）等。
• \S，任意一个非空白字符。与\s匹配内容相反。
• \w，任意一个单词字符，包括数字、大小写字母和下画线。
• \W，任意一个非单词字符。与\w匹配内容相反。

此外，模式中可以使用一对圆括号定义模式组。一个位置可以匹配多个模式时可以使用|符号分隔。如(a|b)表示此位置可以是a也可以是b。

下面的代码判断文本是否以字母a开始。

select 'abc' regexp 'a.';

执行代码会返回1（true）。

下面的代码会使用regexp()函数来判断文本是否包含'a.'。

select regexp('a\.','a.bc');

执行代码会返回1（true）。

下面的代码会判断文本是否包含或不包含a、b、c中的一个字符。

select 'aaa' regexp '[abc]', 'xyz' regexp '[^abc]';

执行代码会返回两个1（true）。第一个文本为aaa，包含a字符，匹配结果为1；第二个模式中使用^符号，表示不包含abc，文本xyz中不包含a、b、c中的字符，匹配结果也是1。

下面的代码会判断文本内容是否包含数字。

select 'aaa' regexp '\d', '123' regexp '\d';

执行代码会返回0（false）和1（true）

下面的代码会判断文本中是否包含空白字符。

select 'a c' regexp '\s', '123' regexp '\s';

执行代码会返回1（true）和0（false）。

下面代码演示了\w模式的应用。

select 'abc' regexp '\w', '+-/' regexp '\w';

执行代码会返回1（true）和0（false）。

匹配次数

指定匹配次数的方法包括：

• *，匹配0次或多次。。
• +，匹配1次或多次。
• ?，匹配0次或1次。
• {n}，匹配n次。
• {n,}，匹配最少n次。
• {m,n}，匹配m到n次。

下面的代码演示了+符号的简单应用。

select 'abc' regexp '[abc]+', 'xyz' regexp '[abc]+';

执行代码会返回1（true）和0（false）。本例中的模式会判断文本中应至少包含一个a、b、c中的字符。

下面的代码会判断文本中是否包含两个连续的a。

select 'abc' regexp 'a{2}', 'aab' regexp 'a{2}';

执行代码会返回0（false）和1（true）。

匹配位置

常用的位置匹配方法如下：

• ^符号定义在模式第一个字符时，表示其后面的模式匹配字符串的开始部分。
• $符号定义在模式最后一个字符时，表示其前面的模式匹配字符串的结束部分。
• \b，表示单词的边界。
• \B，表示非单词边界，与\b含义相反。

下面的代码会判断文本内容是不是以1开始的11位手机号码。

select '12345678910' regexp '^1\d{10}$';

本例，模式定义以1开始（^1），并以10个数字结束（\d{10}$）。执行代码会返回1（true），可以修改文本内容来观察执行结果。

下面的代码判断文本是否包含独立的单词jim。

select 'jim' regexp '\bjim\b', 'jimmy' regexp '\bjim\b';

执行代码会显示1（true）和0（false）。