Oracle拾g的SQL正则表明式扶助,Oracle10g正则表达式

作者: 韦德国际1946国际网址  发布:2019-05-28

Regular expressions in Oracle

With 10g, regular expressions are finally available in SQL. That is, they were already supported through the owa_pattern package.

The new operators and functions are regexp_like, regexp_instr, regexp_substr and regexp_replace

symbol Matches
. Any character except newline
^ Start of a line
$ End of a line
* 0, 1, or more of the preceding element. The preceding element can be grouped with ()
{n} Exactly n repetitions of the preceding element. The preceding element can be grouped with ()
{n,} Matches n or more repetitions of the preceding element. The preceding element can be grouped with ()
{m,n} Matches between m and n repetitions of the preceding element. The preceding element can be grouped with ()
[abc] Character list, matches a, b OR c.
[g-l] g, h, i, j, k, OR l
| groups alternatives

Oracle 八 和Oracle 玖i中缺乏灵活性的SQL 正则表明式最后在Oracle 十g中取得了化解。Oracle 数据库近期内建了适合POSIX 标准的正则表明式。

服务器突然掉电,运维后Oracle数据库运营出现分外,最后开掘是“C:oracleproduct10.2.0db_1database”文件夹下的SPFILE文件出现了难点,使用Oracle命令重新成立一回spfile文件就可以消除,具体解决进程如下:

多少个压缩包表达
Linux-x86-64_一of7.zip database安装介质
Linux-x86-64_2of7.zip database安装介质
Linux-x86-64_三of7.zip grid安装介质
Linux-x86-64_四of七.zip client安装介质
Linux-x86-64_Oracle拾g的SQL正则表明式扶助,Oracle10g正则表达式。5of7.zip gateways安装介质
Linux-x86-64_6of7.zip example
Linux-x86-64_7of7.zip deinstall

Character classes

The following character classes are suppored:

  • [[:alnum:]]
    Alphanumeric characters
  • [[:alpha:]]
    Alphabetic characters
  • [[:blank:]]
    blank space characters
  • [[:cntrl:]]
    Control characters
  • [[:digit:]]
    0 through 9, or [0-9]
  • [[:graph:]]
    [[:punct:]] [[:upper:]] [[:lower:]] [[:digit:]]
  • [[:lower:]]
    lowercase alphabetic characters
  • [[:print:]]
    Printable characters
  • [[:punct:]]
    punctuation characters
  • [[:space:]]
    Non-printing space characters
  • [[:upper:]]
    Uppercase alphabetic characters
  • [[:xdigit:]]
    hexadecimal characters

Character classes can be negated: [^[:CHAR-CLASS:]].

三个新的函数分别是:REGEXP_LIKE、REGEXP_INSTR、REGEXP_SUBSTR、和 REGEXP_REPLACE。它们在用法上与Oracle SQL 函数LIKE、INST奥迪Q5、SUBSTPAJERO和REPLACE 用法,不过它们接纳POSIX 正则表达式替代了老的百分号(%)和通配符(_)字符。

运作cmd,依次运转一下指令完毕spfile文件的重建(相应的参数需求作调度)

11gR2 11.2.0.4:
Linux x86:
https://updates.oracle.com/Orion/Services/download/p13390677_112040_LINUX_1of7.zip?aru=16720989&patch_file=p13390677_112040_LINUX_1of7.zip
https://updates.oracle.com/Orion/Services/download/p13390677_112040_LINUX_2of7.zip?aru=16720989&patch_file=p13390677_112040_LINUX_2of7.zip
https://updates.oracle.com/Orion/Services/download/p13390677_112040_LINUX_3of7.zip?aru=16720989&patch_file=p13390677_112040_LINUX_3of7.zip
https://updates.oracle.com/Orion/Services/download/p13390677_112040_LINUX_4of7.zip?aru=16720989&patch_file=p13390677_112040_LINUX_4of7.zip
https://updates.oracle.com/Orion/Services/download/p13390677_112040_LINUX_5of7.zip?aru=16720989&patch_file=p13390677_112040_LINUX_5of7.zip
https://updates.oracle.com/Orion/Services/download/p13390677_112040_LINUX_6of7.zip?aru=16720989&patch_file=p13390677_112040_LINUX_6of7.zip
https://updates.oracle.com/Orion/Services/download/p13390677_112040_LINUX_7of7.zip?aru=16720989&patch_file=p13390677_112040_LINUX_7of7.zip

Equivalence classes

For example, the following not only matches an a, but also ä.

[[=a=]]  

POSIX 正则表达式由正规的元字符(metacharacters)所构成:

lsnrctl start
set ORACLE_SID=数据实例名
sqlplus /nolog
conn / as sysdba

Linux x86-x64:
https://updates.oracle.com/Orion/Services/download/p13390677_112040_Linux-x86-64_1of7.zip?aru=16716375&patch_file=p13390677_112040_Linux-x86-64_1of7.zip
https://updates.oracle.com/Orion/Services/download/p13390677_112040_Linux-x86-64_2of7.zip?aru=16716375&patch_file=p13390677_112040_Linux-x86-64_2of7.zip
https://updates.oracle.com/Orion/Services/download/p13390677_112040_Linux-x86-64_3of7.zip?aru=16716375&patch_file=p13390677_112040_Linux-x86-64_3of7.zip
https://updates.oracle.com/Orion/Services/download/p13390677_112040_Linux-x86-64_4of7.zip?aru=16716375&patch_file=p13390677_112040_Linux-x86-64_4of7.zip
https://updates.oracle.com/Orion/Services/download/p13390677_112040_Linux-x86-64_5of7.zip?aru=16716375&patch_file=p13390677_112040_Linux-x86-64_5of7.zip
https://updates.oracle.com/Orion/Services/download/p13390677_112040_Linux-x86-64_6of7.zip?aru=16716375&patch_file=p13390677_112040_Linux-x86-64_6of7.zip
https://updates.oracle.com/Orion/Services/download/p13390677_112040_Linux-x86-64_7of7.zip?aru=16716375&patch_file=p13390677_112040_Linux-x86-64_7of7.zip

Links


  • '^' 表示字符串的初步
  • '$' 表示字符串的完毕
  • '.' 表示其他字符
  • 字符的限量,比如说'[a-z]',表示其他ASCII 小写字母,与字符类"[[:lower:]]"" 等价
  • '?' 允许一个后继字符相配零次或一遍
  • ' ' 允许二个后继字符相称三回或频繁
  • '*' 代表零次或频仍

create spfile from pfile=' E:/oracle/product/十.二.0/admin/数据实例名/pfile/init.ora.二壹九一九09203642(各个具体的数据库都不一致)'
startup

Windows x86:
https://updates.oracle.com/Orion/Services/download/p13390677_112040_WINNT_1of6.zip?aru=16919969&patch_file=p13390677_112040_WINNT_1of6.zip
https://updates.oracle.com/Orion/Services/download/p13390677_112040_WINNT_2of6.zip?aru=16919969&patch_file=p13390677_112040_WINNT_2of6.zip
https://updates.oracle.com/Orion/Services/download/p13390677_112040_WINNT_3of6.zip?aru=16919969&patch_file=p13390677_112040_WINNT_3of6.zip
https://updates.oracle.com/Orion/Services/download/p13390677_112040_WINNT_4of6.zip?aru=16919969&patch_file=p13390677_112040_WINNT_4of6.zip
https://updates.oracle.com/Orion/Services/download/p13390677_112040_WINNT_5of6.zip?aru=16919969&patch_file=p13390677_112040_WINNT_5of6.zip
https://updates.oracle.com/Orion/Services/download/p13390677_112040_WINNT_6of6.zip?aru=16919969&patch_file=p13390677_112040_WINNT_6of6.zip

能够使用"{m,n}" 钦赐3个纯正地冒出范围,其意思是“出现从m 次到n 次”;"{m}" 表示“正好m次”;而"{m,}" 表示“至少m次”。仍可以运用圆括号组合字符的成团,使用"|"(竖线)表示可替换。举例,字符串'^([a-z] |[0-9] )$'将协作全部由小写字母或数字组合成的字符串。

Windows x86-x64:
https://updates.oracle.com/Orion/Services/download/p13390677_112040_MSWIN-x86-64_1of7.zip?aru=16908159&patch_file=p13390677_112040_MSWIN-x86-64_1of7.zip
https://updates.oracle.com/Orion/Services/download/p13390677_112040_MSWIN-x86-64_2of7.zip?aru=16908159&patch_file=p13390677_112040_MSWIN-x86-64_2of7.zip
https://updates.oracle.com/Orion/Services/download/p13390677_112040_MSWIN-x86-64_3of7.zip?aru=16908159&patch_file=p13390677_112040_MSWIN-x86-64_3of7.zip
https://updates.oracle.com/Orion/Services/download/p13390677_112040_MSWIN-x86-64_4of7.zip?aru=16908159&patch_file=p13390677_112040_MSWIN-x86-64_4of7.zip
https://updates.oracle.com/Orion/Services/download/p13390677_112040_MSWIN-x86-64_5of7.zip?aru=16908159&patch_file=p13390677_112040_MSWIN-x86-64_5of7.zip
https://updates.oracle.com/Orion/Services/download/p13390677_112040_MSWIN-x86-64_6of7.zip?aru=16908159&patch_file=p13390677_112040_MSWIN-x86-64_6of7.zip
https://updates.oracle.com/Orion/Services/download/p13390677_112040_MSWIN-x86-64_7of7.zip?aru=16908159&patch_file=p13390677_112040_MSWIN-x86-64_7of7.zip

本文由韦德国际1946发布于韦德国际1946国际网址,转载请注明出处:Oracle拾g的SQL正则表明式扶助,Oracle10g正则表达式

关键词: 数据库 数据库开发 Oracle

上一篇:崛起中的玖大HTML5开辟工具
下一篇:没有了