sql的匹配和正则表达式
- 匹配:like 关键字
select * from my_test_copy;
则使用like关键词匹配:注意下划线 '_'和百分号 ‘%’
下划线'_'匹配任意单个字符
百分号'%'匹配任意数目的字符
select * from my_test_copy where name_people like '_满_';
select * from my_test_copy where name_people like '%满%';
select * from my_test_copy where name_people like '%楼';
select * from my_test_copy where name_people like '_楼';
select * from my_test_copy where name_people like '_满楼';
- 正则表达式:‘rlike’ 和 'regexp’同义,用于标识正则表达式模式
(1)‘rlike’ 和 ‘regexp’
select 'abc' regexp 'ab';
select * from my_test_copy where name_people regexp '花';
select * from my_test_copy where name_people rlike '花';
(2)直接匹配
select 'abc' regexp 'b';
select * from my_test_copy where name_people regexp '满';
(3)’^'符号,匹配开头,从字符串开始进行匹配
select * from my_test_copy where name_adress regexp '^天坛';
select * from my_test_copy where name_adress regexp '^北';
(4)’$'符号,匹配结尾
select * from my_test_copy where name_adress regexp '北$';
select * from my_test_copy where name_adress regexp '京$';
(5)点号 ‘.’ 符号,匹配任意一个字符
select * from my_test_copy where name_adress regexp '北.';
select * from my_test_copy where name_adress regexp '.京$';
(6)’+'符号,+前面的模式至少出现1次或以上
select * from my_test_copy where name_adress regexp 'beijing+';
select * from my_test_copy where name_adress regexp '京+';
(7)’*'符号,*前面的模式出现0次或以上
select * from my_test_copy where name_adress regexp '北京*';
select * from my_test_copy where name_adress regexp '人*';
(8)’?‘符号,’?'前面的模式出现0次或1次
select 'abc' regexp '^(ab)?c$';
select 'aaabc' regexp '^a*b?c$';
select 'ababc' regexp '^(ab)+c$';
select * from my_test_copy where name_adress regexp '人?';
select * from my_test_copy where name_adress regexp '京?';
select * from my_test_copy where name_adress regexp '(山庄)?';
select * from my_test_copy where name_adress regexp '山庄?';
(9)’()'符号,表示一个整体
select '33123400' regexp '(34)1';
select 'ac' regexp '^(ab)?c$';
select 'ababc' regexp '^(ab)?c$';
(10)’[]'符号,表示对其中的任意一个字符进行匹配,且仅能匹配一个字符
select 'heo' regexp '^h[abcde]o$';
select 'hello' regexp '^h[abcde]o$';
select 'hello' regexp '^h[abcdehijklmn]o$';
select * from my_test_copy where name_adress regexp '万梅[山庄阵]';
(11)’[]‘中可以使用’-'表示区间,表示该区间的任意一个字符,my_test是另一个表
select 'abcde' regexp '^a[a-k]';
select 'abcde' regexp '[a-k]';
select * from my_test where phone_number regexp '[0-9]';
select * from my_test where phone_number regexp '12[a-z]';
(12)若’[]‘需要匹配’]’,则’]‘必须紧跟在’['之后
select 'aacc' regexp 'a[]a-z]';
select 'aa]cc' regexp 'a[]]';
select 'aa]cc' regexp 'a[bcc]dd]';
select 'aa]cc' regexp 'a[]bcac]';
select 'aa]cc' regexp 'a[bc]]';
(13)若’[]‘需要匹配’-’,则’-‘需要放在’[]'两端,放在中间可能会报错:[Err] 3697 -
The regular expression contains an [x-y] character range where x comes after y.
select 'aaa-ccc-ddd' regexp 'a[-]';
select 'aaa-ccc-ddd' regexp 'a[-cdf]';
select 'aaa-ccc-ddd' regexp 'a[d-a]';
(14)[^]表示不含’[]'中的任意字符
select 'aaabbb' regexp '[^0-9]';
select 'aaabbb' regexp '[^0-9]$';
select 'aaabbb233' regexp '[^0-9]$';
select 'aaabbb' regexp '^[^0-9]';
select '23aaabbb' regexp '^[^0-9]';
select * from my_test_copy where name_adress regexp '[^北京]';
select * from my_test_copy where name_adress regexp '^[^北]';
select * from my_test_copy where name_adress regexp '万梅[^山]';
(15)’|’ 匹配分隔的任意一个字符
select 'abc' regexp 'b|a|c';
select * from my_test_copy where name_adress regexp '北京|南京';
(16)’{t}’ 匹配前面的字符t次
select 'ababc' regexp '^(ab){2}c$';
select 'ababc' regexp '^ab{2}c$';
select 'abbbc' regexp '^ab{3}c$';
(17)’{t,s}'匹配前面的字符t-s次均可,t<=s
select 'abbbc' regexp '^ab{2,4}c$';
select 'abc' regexp '^ab{2,4}c$';
select 'abbbbbbc' regexp '^ab{1,}c$';
(18){t,s}中不能出现空格,否则报错:[Err] 3692 - Incorrect description of a
{min,max} interval.
select 'aoe' regexp '^ao{0 , 1}c$';
原文
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)