A simple REGEXP_REPLACE function Oracle usage

Label Oraclefunction
641 people read comments(0) Collection Report
Classification:

The regular expression pattern string REPLACE function REGEXP_REPLACE lets you search the function. By default,

This function returns to replace each instance of a regular expression pattern of source_char and replace_string.

Returns the string in the same character set source_char.

grammar

The REGEXP_REPLACE function in the Oracle database is grammar:

REGEXP_REPLACE (source_char, pattern, replace_string, position [occurrence], [match_parameter],]])

parameter

Source_char

Character expression of search value. This is usually a character, can be of any data type CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB.

Pattern

regular expression

Value Description
^ Matches the beginning of a string. If used with aMatch_parameterOf "m" it matches the start of a line anywhere withinExpression.
$ Matches the end of a string. If used with aMatch_parameterOf "m" it matches the end of a line anywhere withinExpression.
* Matches zero or more.
+ Matches one or more of the appear.
? Matches zero or one time.
. Matches any character except null.
| Used like an to "OR" specify more than one alternative.
[] Used to specify a matching list where you are trying to match any one of the characters in the list.
[^] Used to specify a nonmatching list where you are trying to match any character except for the ones in the list.
() Used to group expressions as a subexpression.
{m} M times. Matches
{m} At least m times. Matches
{m, n} Matches at least m times, but no more than n times.
\n N is a number between Matches the nth 1 and 9 subexpression found within (before encountering \n.)
[..] Matches one collation element that can be more than one character.
[]: Character classes. Matches
[==] Equivalence classes. Matches
\d Matches a numeric character.
\D Matches a non numeric character.
\w , including any word character underlined.
\W Matches any non - word character.
\s Matches any whitespace characters, including spaces, tabs, formfeed and so on.
\S Matches any non white space character.
\A Matches the beginning of a string or matches at the end of a string before a newline character.
\Z At the end of a Matches string.
*? Matches the preceding pattern zero or more occurrences.
+? Matches the preceding pattern one or more occurrences.
?? Matches the preceding pattern zero or one occurrence.
{n}? The preceding pattern n times. Matches
{n}? Matches the preceding pattern at least n times.
{n, m}? Matches the preceding pattern at least N times, but not more than m times.

Replace_string

Optional. The matching mode will be replaced by replace_string string. If the replace_string parameter is omitted,

All matching mode will be deleted, and returns the result string.

Position

Optional. Search for the start position in the string. If omitted, the default is 1.

Occurrence

Optional. Is a non negative integer default is 1, indicating the occurrence of replacement operation:

If 0 is specified, then all there will be a replacement string.

If the integer n is specified, then the n will replace the time.

Match_parameter

Optional. It allows you to modify the REGEXP_REPLACE function matching behavior. It can be a combination of the following:

Value Description
'C' Case sensitive match.
'I' Case insensitive match.
'n' Allows the period character match the newline (to.) character. By default, the period is a wildcard.
'm' ExpressionIs assumed to have multiple lines, where ^ is the start of a line and $is the end of a line, regardless of the position of those characters inExpressionDefault By,Expression被假定为单行线。
“×” 空格字符被忽略。默认情况下,空白字符匹配像任何其他字符。

例如匹配第一个字

让我们开始使用regexp_replace函数替换字符串中的第一个字。

例如:

选择regexp_replace(“itmyhome是我的网络ID(\)”“幸运”双重;

结果:好运是我的网络标识

这个例子会返回“运气是我的网络ID”,

因为它会再字符串的开始找到第一个匹配的字符,然后替换为“运气”

例如匹配数字字符

我们将使用regexp_replace函数来匹配单个数字字符模式。

例如:

选择regexp_replace(“2,5,和10是这个例子中的数字”' '“#”双重;

结果:“#,#,和# #数字在这个例子

此示例将所指定的\三维数字将以#字符替换

我们可以改变我们的正则模式来搜索仅两位数字。

例如:

选择regexp_replace(“2,5,和10是这个例子中的数字”“(\)”“#”双重;

结果:2,5,和#数字在这个例子

这个例子将替换具有两个数字并排指定的(\)(\)模式。在这种情况下,它将跳过2和5个数字值和用#字符替换10。


作者:itmyhome

猜你在找
查看评论
*以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    版权声明
    本博客文章可以被转载,但未经本人许可,不得用于商业用途。 如需转载,请保证文章的完整性并注明出处。
    个人资料
    • 访问:961039次
    • 积分:一万八千九百七十七
    • 等级:
    • 排名:232名第
    • 原创:471篇
    • 转载:23篇
    • 译文:26篇
    • 评论:1390条
    我的微博
    博客专栏
    文章分类
    博客统计