postgrel查找模糊字段:
例子:
select substring(mech_name from 0 for 4) as mech_name from cib_mechant where mid in (select mid from cib_mechant order by create_time asc limit 1);
使用substring()函数
substring(string [from int] [for int]) | text | 抽取子字串 | substring('Thomas' from 2 for 3) | hom |
substring (string from pattern) | text | 抽取匹配 POSIX 正则表达式的子字串 | substring('Thomas' from '...$') | mas |
substring (string from pattern for escape) | text | 抽取匹配SQL正则表达式的子字串 | substring('Thomas' from '%#"o_a#"_' for '#') | oma |
例子1.
SELECT id, substring(description from '{zh=([^}]+)/}') as description,type FROM t order by id limit 15 offset 0
附limit 和offset的用法:
① selete * from testtable limit 2,1;
② selete * from testtable limit 2 offset 1;
注意:
1.数据库数据计算是从0开始的
2.offset X是跳过X个数据,limit Y是选取Y个数据
3.limit X,Y 中X表示跳过X个数据,读取Y个数据
这两个都是能完成需要,但是他们之间是有区别的:
①是从数据库中第三条开始查询,取一条数据,即第三条数据读取,一二条跳过
②是从数据库中的第二条数据开始查询两条数据,即第二条和第三条
用模糊字段作为查询条件,查找个数,可用like进行拼接:
select * from cib_mechant where mech_name like '%'||(select substring(mech_name from 0 for 4) as mech_name from cib_mechant where mid in (select mid from cib_mechant order by random() limit 1))||'%'