1、REGEXP_LIKE
Sql代码
| SQL> select zip as invalid_zip from person where regexp_like(zip, '[^[:digit:]]'); INVALID_ZIP -------------------- 1b3d5f SQL> select first_name from person where regexp_like(first_name, '^S.*n$'); FIRST_NAME ---------- Steven SQL> select first_name from person where regexp_like(first_name, '^s.*n$'); no rows selected SQL> select first_name from person where regexp_like(first_name, '^s.*n$', 'c'); no rows selected SQL> select first_name from person where regexp_like(first_name, '^s.*n$', 'i'); FIRST_NAME ---------- Steven SQL> select email from person where regexp_like(email, '^james.*com$'); no rows selected SQL> select email from person where regexp_like(email, '^james.*com$', 'n'); -------------------- jamesli@sun.com lijames@oracle.com SQL> select email from person where regexp_like(email, '^li.*com$'); no rows selected SQL> select email from person where regexp_like(email, '^li.*com$', 'm'); -------------------- jamesli@sun.com lijames@oracle.com |
注意上面分别测试了不同的match_option对结果的影响。
2、REGEXP_INSTR
Sql代码
| 查找zip中第一个非数字字符的位置 SQL> select regexp_instr(zip, '[^[:digit:]]') as position from person; POSITION ---------- 0 2 从第三个字符开始，查找zip中第二个非数字字符的位置 SQL> select regexp_instr(zip, '[^[:digit:]]', 3, 2) as position from person; POSITION ---------- 0 6 从第三个字符开始，查找zip中第二个非数字字符的下一个字符位置 SQL> select regexp_instr(zip, '[^[:digit:]]', 3, 2, 1) as position from person; POSITION ---------- 0 7 |
