大家好,在下新建了个ORACLE群,欢迎各位加入,号码:22290972
解决方案 »
- 如何实现excel和oracle数据互相导入
- 我是个初学者,请问各位高手,资料档案库是干什么用的,什么时候用得着呀
- 怎样向ORACLE的日期字段中插入"2005-8-23 16:56:00"类型的日期?
- 删除大量数据后出现问题
- 试问在oracle中 这条语句该如何写
- 一个菜鸟 诚心请问:oracle8i 或9i 的 i是 什么意思,代号?纯粹的版本号?
- ASP 连接oracle8.17出现问题,求助
- 关于 TableSpace 的问题
- 通过jdbc,执行insert语句,每插入50条记录就开始报错?
- oracle forms and reports安装
- 如何写 oracle联合查询分页的sql语句
- 请问OracleDBConsoleoracle 服务因 2 (0x2) 服务性错误而停止。 是什么原因呢?
Description
^
Circumflex. Anchor operator, matches the beginning of a line
$
Dollar. Anchor operator, matches the end of a line
.
Period. Matches any single character
*
Asterisk. Matches zero or more occurrences
?
Question Mark. Matches zero or one occurrences
+
Plus. Matches one or more occurrences
\
Backslash. Treats the next character as a non-metacharacter
{m}
Curly brackets. Match exactly m times
{m,}
Curly brackets. Match at least m times
{m,n}
Curly brackets. Match at least m times, but no more than n times
[ ]
Square brackets. Match any of the characters enclosed within the brackets
|
Pipe character. Alternation operator for specifying alternate patterns
( )
Parentheses. Grouping operator for grouping expressions together
\x
Backslash digit. Backreference expression \1 to \9. Used following an expression in parentheses ( ) to identify the xth occurrence of the backreferenced expression
[:class:]
Character class. Matches any character in the class as defined in the NLS character definitions. Classes include:
[:alnum:] for alphanumeric characters
[:alpha:] for alphabetic characters
[:blank:] for blank space characters
[:cntrl:] for non-printing characters
[:digit:] for numeric characters
[:graph:] includes all characters in classes [:digit:], [:lower:], [:punct:] and [:upper:]
[:lower:] for lowercase alphabetic characters
[:print:] for all printable characters
[:punct:] for all punctuation characters
[:space:] for all nonprinting space characters
[:upper:] for all uppercase alphabetic characters
[:xdigit:] all hexadecimal characters[..]
Collation element delimiters. Used in ranges of character values (for example [a-z] in English or [a-[.ch.]] in Spanish ), these delimiters enclose multiple characters treated as one character such as the ‘ch’ character in Spanish)
[==]
Equivalence class. Matches both accented and unaccented version of a letter
The regular expression '@.*\.COM' matches any string that containing the literal @ together with any characters followed by the literal .com. The following strings match the pattern:@PINTAIL.COM
@oracle.COM
The following strings do not match the pattern:@PINTAIL.ORG -- does not have the required .com
@oracle.com -- will only match if a case insensitive search is performedThe regular expression'\(?[[:digit:]]{3}\)?[- ]?[[:digit:]]{3}[- ]?[[:digit:]]{4}'
matches 10 digit North American phone numbers. The pattern allows, but does not require parentheses around the area code (first three digits). The area code and exchange (the middle three digits) can be separated by either a space, a dash or no character. The exchange and local number (the last four digits) can be separated by either a dash or no character. The breakdown of this patterns is:
Pattern Element
Description
\(?
Match zero or one left parentheses. The left parentheses is a metacharacter, so must be preceded by the backslash.
[[:digit:]]{3}
Match a series of three digits.
\)?
Match zero or one right parentheses. The right parentheses is a metacharacter, so must be preceded by the backslash.
[- ]?
Match zero or one of the bracketed characters, which are a dash and a space.
[[:digit:]]{3}
Match a series of three digits.
[- ]?
Match zero or one of the bracketed characters, which are a dash and a space.
[[:digit:]]{4}
Match a series of four digits.
Description
i
Perform a case-insensitive search. The default is based on NLS_SORT.
c
Perform a case-sensitive search. The default is based on NLS_SORT.
n
Let the dot metacharacter match newline character. By default, the dot will not match newline characters.
m
Treat the source string as multiple lines for purposes of begin of line and end of line anchors ^ and $. The default is to treat the source string as a single string.
Note: If both the i and the c modifiers are included, then the last one takes precedence.
>>ÄÄREGEXP_LIKEÄÄÄ(ÄÄsrcstrÄÂÄ,patternÄÄÄÄÄÄÂÄ)ÄÄÄÄÄÄ><
ÀÄ,modifierÄÄÄÄÄÙ
Click on each parameter in BLUE for an explanation of its use...For the example, look at the sh.customers table in the standard sample schemas for customers having phone numbers in Chicago. The sample schemas are demonstration objects that ship with Oracle 9i and later. They are installed in the seed database and are available for installation in any other database. If you do not have the sample schemas installed in your database, you or your DBA can install them using the Database Configuration Assistant or manually with the $ORACLE_HOME/demo/schema/mk_sample.sql script.The customers within this schema have a phone number that begins with ‘312’. This search does not require regular expressions:SELECT cust_first_name, cust_last_name, cust_main_phone_number
FROM sh.customers
WHERE cust_main_phone_number LIKE '312%'
There are 90 entries found. But some of the phone numbers enclose the area code in parentheses, requiring us to look for strings beginning with either ‘312’ or ‘(312)’. Prior to Oracle10 you would need to multiply predicates in the WHERE clause or create a clever DECODE or REPLACE function call to get the additional 2 customers that have parenthesis in their phone number. With regular expression support in Oracle10, the query can be rewritten like this:SELECT cust_first_name, cust_last_name, cust_main_phone_number
FROM sh.customers
WHERE REGEXP_LIKE(cust_main_phone_number,'^\(?312\)?')
This statement returns the 92 rows. The regular expression begins with the begin of line anchor metacharacter ^ – this tells Oracle that we want to match on patterns that appear in the beginning of our phone number column. Since a left parentheses is a metacharacter, it needs to be preceded with a backslash instructing Oracle to ignore the special nature of this character. Next, use the ‘?’ metacharacter to instruct Oracle to look for zero or one of the preceding (left parentheses) characters. The next part of the patter is the string ‘312’. Since the right parentheses is a metacharacter, it needs to be preceded with the backslash character, just like the left parentheses character. Finally, tell Oracle that the right parentheses may appear zero or one times.But, things aren’t quite done yet. Chicago area codes should include both the 312 and the 773 area codes. The SQL will now look like:SELECT cust_first_name, cust_last_name, cust_main_phone_number
FROM sh.customers
WHERE REGEXP_LIKE(cust_main_phone_number,'^(\(?312\)?|\(?773\)?)')
This statement returns the 93 rows. The regular expression pattern tells Oracle to look for phone numbers that begin with either 312 optionally enclosed with parentheses or 773 optionally enclosed with parentheses. To get this pattern, add the left grouping operator immediately after the begin of line anchor. The alternation operator | is used like an OR keyword to identify an alternate pattern. Finally, add the right grouping operator.
在ora中"%,_"都是通配符号如果我要查带有"_"这个符号的怎么解决.I thinking.
这个不对吧,itrim一定要匹配'abcdefghijklmnopqrstuvwxyz'才行的,不是楼主想要的答案select * from T1 where ascii(substr(name,1,1)) >=97 and ascii(substr(name,1,1)) <=122