в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:08:36Z

1 версия

Новая страница

== Convert a name in the form "first middle last" into the "last, middle first" format==



<source lang="sql">
SQL>
SQL> SELECT REGEXP_REPLACE("Hubert Horatio Hornblower",
2 "(.*) (.*) (.*)",
3 "\3, \2 \1") "Reformatted Name"
4 FROM dual ;
Reformatted Name
--------------------------
Hornblower, Horatio Hubert
SQL></source>

== Regexp_Replace returns the source_string with every occurrence of the pattern_to_find replaced with the replace_string==

The simplest format for this function is:
REGEXP_REPLACE (source_string, pattern_to_find, pattern_to_replace_by)
The general format for the REGEXP_REPLACE function with all the options is:
REGEXP_REPLACE (source_string, pattern_to_find,[pattern_to_replace_by, position, occurrence,match_parameter])



<source lang="sql">
SQL> SELECT REGEXP_REPLACE("Mississippi", "si", "SI", 1, 0, "i")
2 FROM dual;
REGEXP_REPL
-----------
MisSIsSIppi
SQL></source>

== REGEXP_REPLACE(x, pattern [, replace_string [, start [, occurrence [, match_option]]]]) searches x for pattern and replace it with replace_string==

The following example replaces the substring that matches the regular expression l[[: alpha:]]{4} with the string sound using REGEXP_REPLACE():



<source lang="sql">
SQL>
SQL> SELECT REGEXP_REPLACE("abcedfghijklumnoprstuvwxyzabcedfghijklumnoprstuvwxyzabcedfghijklumnoprstuvwxyz", "l[[:alpha:]]{4}", "sound") AS result
2 FROM dual;
RESULT
------------------------------------------------------------------------------
abcedfghijksoundprstuvwxyzabcedfghijksoundprstuvwxyzabcedfghijksoundprstuvwxyz
SQL></source>

== Regext_Replace==

The REGEXP_REPLACE function may have these arguments:



<source lang="sql">
REGEXP_INSTR(String to search, Pattern, [Position,
[Occurrence, [Return-option, [Parameters]]]])</source>

== The Backslash as an Escape Character==



<source lang="sql">
SQL>
SQL> SELECT REGEXP_REPLACE("$1,234.56","$"," ") FROM dual;
REGEXP_REP
----------
$1,234.56
SQL>
SQL> SELECT REGEXP_REPLACE("$1,234.56","\$"," ") FROM dual;
REGEXP_RE
---------
1,234.56
SQL></source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/Regular Expressions Functions/REGEXP REPLACE - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия