`
mayuqi827
  • 浏览: 48867 次
  • 性别: Icon_minigender_1
  • 来自: 深圳
社区版块
存档分类
最新评论

Oracle 查询中使用正则表达式

正则表达式OracleGoSQL 
阅读更多

Oracle SQL 语句中关于正则表达式的函数有
REGEXP_LIKE(匹配)
REGEXP_INSTR (包含)
REGEXP_REPLACE(替换)
REGEXP_SUBSTR(提取)

如 手机号码的表达式： ^[1]{1}[35]{1}[[:digit:]]{9}$

查询客户信息表(tKHXX)中有手机号码(SJHM)的可以这样查询

源码复制打印
  1. SELECT * FORM tKHXX where  REGEXP_LIKE(SJHM,  '^[1]{1}[35]{1}[[:digit:]]{9}$' )  

针对这个表达式解释一下
^ 表示开始
$ 表示结束
[]内部为匹配范围
{}里的内容表时个数

手机号码的特点是以 1开头接着是3或5再加9位的数字 所以这么理解
1开头 表达式为 ^[1]{1} 意为 开始1位里包含1
3或5 表达式为 [35]{1}
9位数字结束 为: [[:digit:]]{9}$ 这里[:digit:]为特殊写法，代表为数字 再加个结束符$

用则表达式很简单，更高效
下面列一些参考，来自网络 :)

Anchoring Characters
^ Anchoring Characters
$ Anchor the expression to the end of a line

Equivalence Classes
= =
Oracle supports the equivalence classes through the POSIX '[==]' syntax. A base letter and all of its accented versions constitute an equivalence class. For example, the equivalence class '[=a=]' matches ?and ? The equivalence classes are valid only inside the bracketed expression
Match Options
c Case sensitive matching
i Case insensitive matching
m Treat source string as multi-line activating Anchor chars
n Allow the period (.) to match any newline character
Posix Characters

[:alnum:] Alphanumeric characters
[:alpha:] Alphabetic characters
[:blank:] Blank Space Characters
[:cntrl:] Control characters (nonprinting)
[:digit:] Numeric digits
[:graph:] Any [:punct:], [:upper:], [:lower:], and [:digit:] chars
[:lower:] Lowercase alphabetic characters
[:print:] Printable characters
[:punct:] Punctuation characters
[:space:] Space characters (nonprinting), such as carriage return, newline, vertical tab, and form feed
[:upper:] Uppercase alphabetic characters
[:xdigit:] Hexidecimal characters
Quantifier Characters

* Match 0 or more times
? Match 0 or 1 time
+ Match 1 or more times
{m} Match exactly m times
{m,} Match at least m times
{m, n} Match at least m times but no more than n times
\n Cause the previous expression to be repeated n times

Alternative Matching And Grouping Characters
| Separates alternates, often used with grouping operator ()
( ) Groups subexpression into a unit for alternations, for quantifiers, or for backreferencing (see "Backreferences" section)
[char] Indicates a character list; most metacharacters inside a character list are understood as literals, with the exception of character classes, and the ^ and - metacharacters

下面是个测试例子及环境

测试表

源码复制打印
  1. CREATE TABLE test (   
  2. testcol VARCHAR2(50));   
  3.   
  4. INSERT INTO test VALUES ('abcde');   

  1. INSERT INTO test VALUES ('12345');   
  2. INSERT INTO test VALUES ('1a4A5');   
  3. INSERT INTO test VALUES ('12a45');   
  4. INSERT INTO test VALUES ('12aBC');   
  5. INSERT INTO test VALUES ('12abc');   
  6. INSERT INTO test VALUES ('12ab5');   
  7. INSERT INTO test VALUES ('12aa5');   
  8. INSERT INTO test VALUES ('12AB5');   
  9. INSERT INTO test VALUES ('ABCDE');   
  10. INSERT INTO test VALUES ('123-5');   
  11. INSERT INTO test VALUES ('12.45');   
  12. INSERT INTO test VALUES ('1a4b5');   
  13. INSERT INTO test VALUES ('1 3 5');   
  14. INSERT INTO test VALUES ('1  45');   
  15. INSERT INTO test VALUES ('1   5');   
  16. INSERT INTO test VALUES ('a  b  c  d');   
  17. INSERT INTO test VALUES ('a b  c   d    e');   
  18. INSERT INTO test VALUES ('a              e');   
  19. INSERT INTO test VALUES ('Steven');   
  20. INSERT INTO test VALUES ('Stephen');   
  21. INSERT INTO test VALUES ('111.222.3333');   
  22. INSERT INTO test VALUES ('222.333.4444');   
  23. INSERT INTO test VALUES ('333.444.5555');   
  24. COMMIT;  



REGEXP_INSTR
REGEXP_INSTR(<source_string>, <pattern>, <start_position>, <occurrence>, <return_option>, <match_parameter>)

Find words beginning with 's' or 'r' or 'p' followed by any 4 alphabetic characters: case insensitive

源码复制打印
  1. SELECT REGEXP_INSTR('500 Oracle Pkwy, Redwood Shores, CA', '[o][[:alpha:]]{3}', 1, 1, 0, 'i') RESULT   
  2. FROM dual;   
  3.   
  4. SELECT REGEXP_INSTR('500 Oracle Pkwy, Redwood Shores, CA', '[o][[:alpha:]]{3}', 1, 1, 1, 'i') RESULT   
  5. FROM dual;   
  6.   
  7. SELECT REGEXP_INSTR('500 Oracle Pkwy, Redwood Shores, CA', '[o][[:alpha:]]{3}', 1, 2, 0, 'i') RESULT   
  8. FROM dual;   
  9.   
  10. SELECT REGEXP_INSTR('500 Oracle Pkwy, Redwood Shores, CA', '[o][[:alpha:]]{3}', 1, 2, 1, 'i') RESULT   
  11. FROM dual;  



Find the postiion of try, trying, tried or tries

源码复制打印
  1. SELECT REGEXP_INSTR('We are trying to make the subject easier.', 'tr(y(ing)?|(ied)|(ies))') RESULTNUM   
  2. FROM dual;  




REGEXP_LIKE

REGEXP_LIKE(<source_string>, <pattern>, <match_parameter>)

AlphaNumeric Characters

源码复制打印
  1. SELECT *   
  2. FROM test   
  3. WHERE REGEXP_LIKE(testcol, '[[:alnum:]]');   
  4.   
  5. SELECT *   
  6. FROM test   
  7. WHERE REGEXP_LIKE(testcol, '[[:alnum:]]{3}');   
  8.   
  9. SELECT *   
  10. FROM test   
  11. WHERE REGEXP_LIKE(testcol, '[[:alnum:]]{5}');  



Alphabetic Characters:

源码复制打印
  1. SELECT *   
  2. FROM test   
  3. WHERE REGEXP_LIKE(testcol, '[[:alpha:]]');   
  4.   
  5. SELECT *   
  6. FROM test   
  7. WHERE REGEXP_LIKE(testcol, '[[:alpha:]]{3}');   
  8.   
  9. SELECT *   
  10. FROM test   
  11. WHERE REGEXP_LIKE(testcol, '[[:alpha:]]{5}');  



Control Characters

源码复制打印
  1. INSERT INTO test VALUES ('zyx' || CHR(13) || 'wvu');   
  2. COMMIT;   
  3.   
  4. SELECT *   
  5. FROM test   
  6. WHERE REGEXP_LIKE(testcol, '[[:cntrl:]]{1}');  



Digits

源码复制打印
  1. SELECT *   
  2. FROM test   
  3. WHERE REGEXP_LIKE(testcol, '[[:digit:]]');   
  4.   
  5. SELECT *   
  6. FROM test   
  7. WHERE REGEXP_LIKE(testcol, '[[:digit:]]{3}');   
  8.   
  9. SELECT *   
  10. FROM test   
  11. WHERE REGEXP_LIKE(testcol, '[[:digit:]]{5}');  




Lower Case

源码复制打印
  1. SELECT *   
  2. FROM test   
  3. WHERE REGEXP_LIKE(testcol, '[[:lower:]]');   
  4.   
  5. SELECT *   
  6. FROM test   
  7. WHERE REGEXP_LIKE(testcol, '[[:lower:]]{2}');   
  8.   
  9. SELECT *   
  10. FROM test   
  11. WHERE REGEXP_LIKE(testcol, '[[:lower:]]{3}');   
  12.   
  13. SELECT *   
  14. FROM test   
  15. WHERE REGEXP_LIKE(testcol, '[[:lower:]]{5}');  




Printable Characters

源码复制打印
  1. SELECT *   
  2. FROM test   
  3. WHERE REGEXP_LIKE(testcol, '[[:print:]]{5}');   
  4.   
  5. SELECT *   
  6. FROM test   
  7. WHERE REGEXP_LIKE(testcol, '[[:print:]]{6}');   
  8.   
  9. SELECT *   
  10. FROM test   
  11. WHERE REGEXP_LIKE(testcol, '[[:print:]]{7}');   



Punctuation

源码复制打印
  1. TRUNCATE TABLE test;   
  2.   
  3. SELECT *   
  4. FROM test   
  5. WHERE REGEXP_LIKE(testcol, '[[:punct:]]');  




Spaces

源码复制打印
  1. SELECT *   
  2. FROM test   
  3. WHERE REGEXP_LIKE(testcol, '[[:space:]]');   
  4.   
  5. SELECT *   
  6. FROM test   
  7. WHERE REGEXP_LIKE(testcol, '[[:space:]]{2}');   
  8.   
  9. SELECT *   
  10. FROM test   
  11. WHERE REGEXP_LIKE(testcol, '[[:space:]]{3}');   
  12.   
  13. SELECT *   
  14. FROM test   
  15. WHERE REGEXP_LIKE(testcol, '[[:space:]]{5}');  





Upper Case

源码复制打印
  1. SELECT *   
  2. FROM test   
  3. WHERE REGEXP_LIKE(testcol, '[[:upper:]]');   
  4.   
  5. SELECT *   
  6. FROM test   
  7. WHERE REGEXP_LIKE(testcol, '[[:upper:]]{2}');   
  8.   
  9. SELECT *   
  10. FROM test   
  11. WHERE REGEXP_LIKE(testcol, '[[:upper:]]{3}');   




Values Starting with 'a%b'

源码复制打印
  1. SELECT testcol   
  2. FROM test   
  3. WHERE REGEXP_LIKE(testcol, '^ab*');   




'a' is the third value

源码复制打印
  1. SELECT testcol   
  2. ROM test  WHERE REGEXP_LIKE(testcol, '^..a.');   




Contains two consecutive occurances of the letter 'a' or 'z'

源码复制打印
  1. SELECT testcol  FROM test  WHERE REGEXP_LIKE(testcol, '([az])\1', 'i');   



Begins with 'Ste' ends with 'en' and contains either 'v' or 'ph' in the center

源码复制打印
  1. SELECT testcol FROM test WHERE REGEXP_LIKE(testcol, '^Ste(v|ph)en$');   




Use a regular expression in a check constraint

源码复制打印
  1. CREATE TABLE mytest (c1 VARCHAR2(20),    
  2. CHECK (REGEXP_LIKE(c1, '^[[:alpha:]]+$')));    
  3. Identify SSN   
  4.   
  5. Thanks: Byron Bush HIOUG    
  6.   
  7.   
  8. CREATE TABLE ssn_test (   
  9. ssn_col  VARCHAR2(20));   
  10.   
  11. INSERT INTO ssn_test VALUES ('111-22-3333');   
  12. INSERT INTO ssn_test VALUES ('111=22-3333');   
  13. INSERT INTO ssn_test VALUES ('111-A2-3333');   
  14. INSERT INTO ssn_test VALUES ('111-22-33339');   
  15. INSERT INTO ssn_test VALUES ('111-2-23333');   
  16. INSERT INTO ssn_test VALUES ('987-65-4321');   
  17. COMMIT;   
  18.   
  19. SELECT ssn_col   
  20. from ssn_test   
  21. WHERE regexp_like(ssn_col,'^[0-9]{3}-[0-9]{2}-[0-9]{4}$');   



REGEXP_REPLACE

Syntax REGEXP_REPLACE(<source_string>, <pattern>,<replace_string>, <position>, <occurrence>, <match_parameter>)


Looks for the pattern xxx.xxx.xxxx and reformats pattern to (xxx) xxx-xxxx col testcol format a15
col result format a15

源码复制打印
  1. SELECT testcol, REGEXP_REPLACE(testcol,'([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',   
  2. '(\1) \2-\3') RESULT   
  3. FROM test   
  4. WHERE LENGTH(testcol) = 12;   




Put a space after every character

源码复制打印
  1. SELECT testcol, REGEXP_REPLACE(testcol, '(.)', '\1 ') RESULT   
  2. FROM test   WHERE testcol like 'S%';   




Replace multiple spaces with a single space

源码复制打印
  1. SELECT REGEXP_REPLACE('500    Oracle    Parkway, Redwood    Shores, CA', '( ){2,}', ' ') RESULT   
  2. FROM dual;   




Insert a space between a lower case character followed by an upper case character

源码复制打印
  1. SELECT REGEXP_REPLACE('George McGovern', '([[:lower:]])([[:upper:]])', '\1 \2') CITY   
  2. FROM dual;   


Replace the period with a string (note use of '\')

源码复制打印
  1.   
  2. SELECT REGEXP_REPLACE('We are trying to make the subject easier.','\.',' for you.') REGEXT_SAMPLE   
  3. FROM dual;   



REGEXP_SUBSTR

Syntax REGEXP_SUBSTR(source_string, pattern[, position [, occurrence[, match_parameter]]])


Searches for a comma followed by one or more occurrences of non-comma characters followed by a comma

源码复制打印
  1. SELECT REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA', ',[^,]+,') RESULT   
  2. FROM dual;   




Look for http:// followed by a substring of one or more alphanumeric characters and optionally, a period (.) col result format a50

源码复制打印
  1. SELECT REGEXP_SUBSTR('Go to http://www.oracle.com/products and click on database',   
  2. 'http://([[:alnum:]]+\.?){3,4}/?') RESULT   
  3. FROM dual;   




Extracts try, trying, tried or tries


SELECT REGEXP_SUBSTR('We are trying to make the subject easier.','tr(y(ing)?|(ied)|(ies))')
FROM dual;
Extract the 3rd field treating ':' as a delimiter SELECT REGEXP_SUBSTR('system/pwd@orabase:1521:sidval',
'[^:]+', 1, 3) RESULT
FROM dual;

Extract from string with vertical bar delimiter

源码复制打印
  1.   
  2. CREATE TABLE regexp (   
  3. testcol VARCHAR2(50));   
  4.   
  5. INSERT INTO regexp   
  6. (testcol)   
  7. VALUES  
  8. ('One|Two|Three|Four|Five');   
  9.   
  10. SELECT * FROM regexp;   
  11.   
  12. SELECT REGEXP_SUBSTR(testcol,'[^|]+', 1, 3)   
  13. FROM regexp;   


分享到：
| 经典SQL语句大全
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    Oracle中的正则表达式

    * 元字符和普通字符：元字符是指在正则表达式中表示特殊含义的字符，如"."、"\"、"?"、"*"、"+"、"|",等等。普通字符是指除元字符外的所有 Unicode 字符。 * 量词：量词用来指定量词的前一个字符出现的次数，如"?",...

    Oracle数据库正则表达式

    同时，正则表达式也可以与其他 Oracle 函数结合使用，例如 substr()、replace()、instr() 等，来实现更加复杂的数据处理任务。 正则表达式是 Oracle 数据库中的一种强大的工具，可以帮助用户快速、高效地处理数据。

    Oracle中使用正则表达式

    #### 三、Oracle正则表达式的实现特点 Oracle数据库中正则表达式的实现遵循POSIX标准（由电气和电子工程师协会(IEEE)颁布）的ASCII数据匹配语义和语法。但Oracle也扩展了这些标准，增加了更多的多语言支持，使得在...

    Oracle正则表达式详解（用法+实例）

    ### Oracle正则表达式详解（用法+实例） #### 一、正则表达式简介 正则表达式是一种用于匹配字符串中字符组合的工具。它由一个或多个字符及特殊的字符元组成，能够帮助我们执行复杂的字符串搜索和替换任务。在...

    Oracle 10g 针对正则表达式的支持分析

    - **集成到SQL语句中**：可以直接在SQL查询中使用正则表达式进行条件过滤，无需编写额外的程序代码。 ##### 3.2 实现方法 - **REGEXP_LIKE**：用于判断一个字符串是否符合指定的正则表达式模式。 - **REGEXP_INSTR*...

    ORACLE正则表达式

    ORACLE 正则表达式的使用(REGEXP_LIKE REGEXP_INSTR REGEXP_SUBSTR REGEXP_REPLACE)

    正则表达式在oracle中的应用

    2. **错误排查**：正则表达式中的错误排查通常比标准SQL语句更加困难。开发者需要熟悉正则表达式的语法和特性，以便能够准确地定位和解决问题。 3. **性能考虑**：尽管正则表达式提供了强大的查询能力，但在某些...

    Oracle正则表达式函数全面解析

    在方括号表达式中使用时，表示不接受该字符集合。 - `$`：匹配输入字符串的结尾位置；如果设置了多行模式，则还匹配`\n`或`\r`。 - `.`：匹配除换行符之外的任何单个字符。 - `?`：匹配前面的子表达式零次或一次。 -...

    Oracle Regular Expressions 数据库 正则表达式

    - **元字符**: 在正则表达式中，有一些特殊字符，如`.`代表任意字符，`^`表示行首，`$`表示行尾，`\`用于转义特殊字符。 - **量词**: `{n}`表示匹配n次，`{n,}`表示至少匹配n次，`{n,m}`表示匹配n到m次。 - **...

    ORACLE.PLSQL正则表达式概述

    例如，在`WHERE`子句中使用正则表达式可能无法利用索引，导致全表扫描，从而影响查询效率。 ### 结论 ORACLE.PLSQL中的正则表达式功能为数据库操作提供了极大的灵活性和便利性。无论是数据清洗、格式化还是复杂的...

    oracle正则表达式

    PDF文件"Oracle正则表达式函数介绍.pdf"很可能详细介绍了这些函数的使用方法、模式语法和实例，而"Oracle_正则表达式.txt"可能包含了更多示例或使用技巧。对于学习和理解Oracle正则表达式，这两个文件都是宝贵的资源...

    oracle 正则表达式详细讲解

    oracle 数据库中 正则表达式 详细讲解 ，保证看了之后对正则表达式有一个深入的理解。对于不会用正则表达式的朋友非常有帮助。

    正则表达式在Oracle中的应用与实现.pdf

    Oracle数据库中正则表达式的应用是指在Oracle数据库管理系统中使用正则表达式来实现高效的数据检索和处理。正则表达式是一种源于Unix的强大的数据检索技术，可以对搜索条件进行灵活的控制，对于规范的格式如电话号、...

    ORACLESQL正则表达式.pdf

    正则表达式是在 Oracle 10g 中添加的新特性，用于匹配字符串中的模式。下面是 ORACLE SQL 正则表达式的相关知识点： 匹配字符 * `[:alnum:]` 匹配任何字母和数字 * `[:alpha:]` 匹配任何字母 * `[:blank:]` 匹配...

    oracle正则表达式总结

    正则表达式中的运算符具有不同的优先级： - `\` 转义符 - `(), (?:), (?=), []` 圆括号和方括号 - `*, +, ?, {n}, {n,}, {n,m}` 限定符 - `^, $, \b, \B, \A, \Z, \z` 位置和顺序 - `|` “或”操作 #### 三、Oracle...

    正则表达式入门指导.ppt

    正则表达式中的`.`具有广泛性，它能匹配任何字符，包括空格和特殊字符。这有时会导致非预期的结果。为了解决这个问题，可以使用**字符类**（如`[a-z]`表示小写字母）或**否定预查**（如`[^a-z]`表示除小写字母外的...

    各类正则表达式详细手册

    在Oracle数据库中，PLSQL（Procedural Language/Structured Query Language）提供了REGEXP_LIKE函数来支持正则表达式操作。这些功能允许开发者进行复杂的字符串匹配和处理。例如，`REGEXP_LIKE(column, pattern)` ...

Magicbox
Global site tag (gtag.js) - Google Analytics