Good afternoon. Faced here a problem, I carry out on public court
There is a certain system which works with telephone numbers. The system allows  a mask for a field.
In mask record following rules are used:
9 - on this place the digit is underlined
\&lt;Character&gt; - the constant character
Thus, for phone of the Russian Federation the mask can be written down in a type

`` \+ \7 \(999 \) 999\-99\-99 or 999\-999\-99\-99 or \9\-\8\-9999999999 \; ``

The task 1 - request to format saved telephone numbers on a mask. The request should work for the arbitrary mask which have been written down by rules (it is considered only numbers of the Russian Federation which have been written down in a format of 10 digits, for example, 4959009876 or 8005554212, correctness of a mask and to check it is not necessary is provides the system)

``````SQL&gt; with m as (select ' \+\7 \(999 \) 999\-99\-99 ' mask, ' 4959009876 ' num from dual)
...
...
...
14 select phone_num from (
...
16);
PHONE_NUM
--------------------------------------------------------------------------------
+7(495) 900-98-76``````

The task 2 - request to remove a mask from numbers which have been written down in a format of a mask (the system transfers numbers according to a mask to check on a correctness it is not required - i.e. +7 (495) 900-98-76 to transform in 4959009876). The request as should work for the arbitrary mask.

``````SQL&gt; with m as (select ' \+\7 \(999 \) 999\-99\-99 ' mask, ' +7(495) 900-98-76 ' phone_num from dual)
...
...
...
10 select num from (
...
12);
NUM
--------------------------------------------------------------------------------
4959009876
SQL&gt;``````

slow brain wrote:

``````select regexp_substr (regexp_replace (' +7(495) 900-98-76 ', '\D '), '\d {10} \$ ') as n from dual;
N
----------
4959009876``````

Elic;
Beautifully, but if the kind user drives a mask with a numeral constant on the end - does not work

``````SQL&gt; with m as (select ' \+\7 \(999 \) 999\-99\-99 \;\1 ' mask, ' +7(495) 900-98-76; 1 ' phone_num from dual)
..
..
..
10 select num from (
...
12);
NUM
--------------------------------------------------------------------------------
4959009876``````

slow brain wrote:

Elic;
Beautifully, but if the kind user drives a mask with a numeral constant on the end - does not work

But after all you told that masks only three and the correctness of the gated in phone is checked by "system".

slow brain wrote:

the Request should work for as arbitrary the mask which have been written down by rules

That I resulted that is only examples, the imagination is not restricted to it.
Under the task 2 by the way I found two different variants. regexp there are in both, but they in essence different

But after all you told that masks only three and the correctness of the gated in phone is checked by "system".

Correctness in that is not required to check syntax of a mask on a correctness, and as
In the task 1 it is not required to check number of significant positions in a mask and in number - they coincide (if does not coincide, it is discarded earlier)
In the task 2 it is not required to check coincidence of the input data to a mask - just it and the system provides.

slow brain;
The accurate and consistent description as well as that  is necessary. And examples. Personally I while understood nothing ((.

It seemed to me that is described normally.
Essence - number written down as 10 digits to display in a type set by a mask, and on the contrary if number is written down on a mask, including different . Characters (including they can be numeral and in the arbitrary places) - these . to remove. In a mask "9 character designates the digit position, screening \designates the character following it as is. The mask can be absolute arbitrary, but should register by rules (i.e. any character not being"9"screens,"9"can be shielded)

Examples:

``````with m as (select ' 999\-999\-99\-99 ' mask, ' 4959009876 ' num from dual)
..
select phone_num from (
);
495-900-98-76
with m as (select '\9\-\8\-9999999999 \; ' mask, ' 4959009876 ' num from dual)
..
select phone_num from (
);
9-8-4959009876;
with m as (select ' \+\7 \(999 \) 999\-99\-99 \;\1 ' mask, ' 4959009876 ' num from dual)
..
select phone_num from (
);
+7(495) 900-98-76; 1``````

In the opposite direction I think clearly.

slow brain wrote:

Elic;
Beautifully, but if the kind user drives a mask with a numeral constant on the end - does not work

The task needs to be sounded distinctly. And not clearly with what  digits will be used as separators. Well it is fine. If number of groups &lt;= 9:

``````with m as (
select ' 999\-999\-99\-99 ' mask, ' 495-900-98-76 ' phone_num from dual union all
select '\9\-\8\-9999999999 \; ' mask, ' 9-8-4959009876; ' phone_num from dual union all
select ' \+\7 \(999 \) 999\-99\-99 \;\1 ' mask, ' +7(495) 900-98-76; 1 ' phone_num from dual
)
phone_num;
regexp_replace (
phone_num;
regexp_replace (regexp_replace (mask, ' \\. ', '. '), ' 9 + ', ' (\d +) ');
'\1\2\3\4\5\6\7\8\9'
) num
from m
/
-------------------------- -------------------- ----------
999\-999\-99\-99 495-900-98-76 4959009876
\9\-\8\-9999999999 \; 9-8-4959009876; 4959009876
\+\7 \(999 \) 999\-99\-99 \;\1 +7(495) 900-98-76; 1 4959009876
SQL&gt;``````

SY.

slow brain wrote:

I Think because of restrictions   without a partition of a line in this or that type does not dare for number of groups of the nine more than 9.
, or a symbol-by-symbol partition or  groups of the nine of the initial number  over a mask.

``````SQL&gt; with m as
2 (select 1 id, ' \+\7 \(999 \) 999\-99\-99 ' mask, ' +7(495) 900-98-76 ' phone_num from dual
3 union all select 2 id, ' \+\7 \(999 \) 999\-99\-99 \;\1 ' mask, ' +7(495) 900-98-76; 1 ' phone_num from dual
4)
5 select *
6 from m
7 model
8 partition by (id)
9 dimension by (1 dummy)
10 measures (replace (mask, ' \') mask, phone_num, cast (null as varchar2 (240)) result)
11 rules iterate (1e9) until (substr (mask [1], iteration_number + 1, 1) is null)
12 (
13 result [1] = decode (substr (mask [1], iteration_number + 1, 1), ' 9 ';
14 result [1] || substr (phone_num [1], iteration_number + 1, 1), result [1])
15);
---------- ---------- -------------------------- ------------------ --------------------
1 1 +7(999)999-99-99 +7(495) 900-98-76 4959009876
2 1 +7(999) 999-99-99; 1 +7(495) 900-98-76; 1 4959009876
SQL&gt; with m as
2 (select 1 id, ' \+\7 \(999 \) 999\-99\-99 ' mask, ' +7(495) 900-98-76 ' phone_num from dual
3 union all select 2 id, ' \+\7 \(999 \) 999\-99\-99 \;\1 ' mask, ' +7(495) 900-98-76; 1 ' phone_num from dual
4)
5 select id;
6 listagg (regexp_substr (phone_num_mod, mask_mod, 1, 1, null, level)) within group (order by level) result
7 from (select id;
8 translate (phone_num, ' () ', ' ## ') phone_num_mod;
9 regexp_replace (regexp_replace (translate (mask, ' () \', ' ## '), ' (9 +) ', ' (\1) '), ' [^ ()] ', '. ') mask_mod
10 from m)
11 connect by prior id = id and level &lt;= regexp_count (mask_mod, ' \(') and prior sys_guid () is not null
12 group by id;
ID RESULT
---------- --------------------
1 4959009876
2 4959009876``````

With the second task almost understood
Method No2 - it is pure on regexp (I made it after a method No1) -  with count of an amount of groups

``````with m as (select ' \+\7 \(999 \) 999\-99\-99\-\1 ' mask, ' +7 (495) 900-98-76-1 ' phone_num from dual)
, fmt1 as (select phone_num, replace (replace (regexp_replace (mask, ' \\. ', '. '), ' 9 ', ' (\d) '), ') (') mask_str from m)
, fmt as (select phone_num
, listagg (txt, ") within group (order by txt) format_str
from fmt1
, (select ' \' || rownum txt from fmt1 connect by level &lt;= regexp_count (mask_str, ' \('))
)
select num from (
select regexp_replace (phone_num, mask_str, format_str) num from fmt);``````

And from here and to the decision of the task 1 nearby

dbms_photoshop wrote:

I Think because of restrictions   without a partition of a line in this or that type does not dare for number of groups of the nine more than 9.

It was the method No1 - I invented it earlier - simply recalling that  "mask" can be treated as "bit mask" - and it has no  on groups

``````with m as (
select ' 999\-999\-99\-99 ' mask, ' 495-900-98-76 ' phone_num from dual union all
select '\9\-\8\-9999999999 \; ' mask, ' 9-8-4959009876; ' phone_num from dual union all
select ' \+\7 \(999 \) 999\-99\-99 \;\1 ' mask, ' +7(495) 900-98-76; 1 ' phone_num from dual
)
phone_num;
replace (utl_raw.cast_to_varchar2 (utl_raw.bit_and (utl_raw.cast_to_raw (phone_num), utl_raw.cast_to_raw (replace (regexp_replace (mask, ' \\. ', chr (0)), ' 9 ', chr (255))))), chr (0))
from m``````

slow brain wrote:

it is pure on regexp

to cut and splice means "purely on regexp"?:-D
As it has already been told, because of restrictions   for the general case it is necessary to cut and glue;
Therefore tensioning of this algorithm on SQL no more than overindulgence.
Usage built in PL/SQL packets for RAW - too is simple  an eye.

Under the task No1 I will publish the decision on Monday.

Did not solve while a variant c ' \\', final replace it is possible to replace any artful .

``````with mask (id, mask, num) as (
select 1, ' 999\-999\-99\-99 ', ' 4959009876 ' from dual
union all
select 2, ' \+\7 \(999 \) 999\-99\-99 \;\1 ', ' 4959009876 ' from dual
union all
select 3, '\9\-\8\-9999999999 \; ', ' 4959009876 ' from dual
);
parse (id, mask, num, lvl, MOSCOW TIME) as (
select
id, mask, num, 1, regexp_replace (mask, ' 9 {1} ', substr (num, 1, 1), instr (replace (mask, '\9 ', '\x '), ' 9 ', 1, 1), 1) MOSCOW TIME
union all
select
m.id, m.mask, m.num, p.lvl+1, regexp_replace (p.msk, ' 9 {1} ', substr (m.num, p.lvl+1, 1), instr (replace (m.mask, '\9 ', '\x '), ' 9 ', 1, p.lvl+1), 1) MOSCOW TIME
from parse p
join mask m on m.id = p.id and p.lvl+1 &lt;= length (m.num)
)
select
mask, num, replace (MOSCOW TIME, ' \', ") as result
from parse
where lvl = length (num);``````

slow brain wrote:

With the second task almost understood
Method No2 - it is pure on regexp (I made it after a method No1) -  with count of an amount of groups

``````with m as (select ' \+\7 \(999 \) 999\-99\-99\-\1 ' mask, ' +7 (495) 900-98-76-1 ' phone_num from dual)
, fmt1 as (select phone_num, replace (replace (regexp_replace (mask, ' \\. ', '. '), ' 9 ', ' (\d) '), ') (') mask_str from m)
, fmt as (select phone_num
, listagg (txt, ") within group (order by txt) format_str
from fmt1
, (select ' \' || rownum txt from fmt1 connect by level &lt;= regexp_count (mask_str, ' \('))
)
select num from (
select regexp_replace (phone_num, mask_str, format_str) num from fmt);``````

You too complicated - all this listagg at all e. The link on nonexistent backreference is substituted NULL so-that it is enough '\1\2\3\4\5\6\7\8\9 '. And groups of one digit to create too it is unnecessary - them after all can be only 9.
Use not (9)-&gt; (\d) a (9 +)-&gt; (\d +) as in my example:

``````regexp_replace (
phone_num;
regexp_replace (regexp_replace (mask, ' \\. ', '. '), ' 9 + ', ' (\d +) ');
'\1\2\3\4\5\6\7\8\9'
) num``````

SY.

SY;
I would launch your variant, if the task suddenly did not remove

slow brain wrote:

under the task No1 I will publish The decision on Monday.

``````with m as (
select ' 999\-999\-99\-99 ' mask, ' 4959009876 ' num from dual union all
select '\9\-\8\-9999999999 \; ' mask, ' 4959009876 ' num from dual union all
select ' \+\7 \(999 \) 999\-99\-99 \;\1 ' mask, ' 4959009876 ' num from dual
);
r (
num;
phone_num;
num_pos
) as (
num;
null phone_num;
0 num_pos
from m
union all
num;
else phone_num || substr (num, num_pos + 1,1)
end phone_num;
when ' \' then mask_pos + 2
when ' \' then num_pos
else num_pos + 1
end num_pos
from r
)
num;
phone_num
from r
/
-------------------------- ---------- --------------------
999\-999\-99\-99 4959009876 495-900-98-76
\9\-\8\-9999999999 \; 4959009876 9-8-4959009876;
\+\7 \(999 \) 999\-99\-99 \;\1 4959009876 +7(495) 900-98-76; 1
SQL&gt;``````

SY.

SY;
And this is for what purpose published?
I understand rpovarov can sharpens skill with rec with, but in your case in what sense?
If on SQL there is an attempt to stretch not SQL the task so let's it make still the most crazy method?
If we have 10 million masks on 15 characters so let's push in workarea 150 million lines and on them we will run.
Then  a dial-up of the last iteration.
But SQL.

dbms_photoshop wrote:

If we have 10 million masks on 15 characters so let's push in workarea 150 million lines and on them we will run.

Well and why you solved that "10 million masks"? Them can be rather the limited number. Also it will be caused for example only when the client came on a site to show its number in customary for geography of the client to the form. Though it agree, to store and number and "" number more reasonably.
SY.

here only in a forehead (plaid about without looking other posts except start):
[spoiler a variant 1]

``````with m as (select ' \+\7 \(999 \) 999\-99\-99 ' mask, ' 4959009876 ' num from dual)
select
regexp_replace (
regexp_replace (
,' 9 (\\.)*9 (\\.)*9 (\\.)*9 (\\.)*9 (\\.)*9 (\\.)*9 (\\.)*9 (\\.)*9 (\\.)*9\$'
, substr (num, 1,1) || '\1'
||substr (num, 2,1) || '\2'
||substr (num, 3,1) || '\3'
||substr (num, 4,1) || '\4'
||substr (num, 5,1) || '\5'
||substr (num, 6,1) || '\6'
||substr (num, 7,1) || '\7'
||substr (num, 8,1) || '\8'
||substr (num, 9,1) || '\9'
||substr (num, 10,1)
)
,' \\(.) ', '\1 ')
from m;``````

[/spoiler]
[spoiler a variant 2]

``````with m as (select ' \+\7 \(999 \) 999\-99\-99 ' mask, ' 4959009876 ' num from dual)
select
m.*
,regexp_replace (
(select
listagg (
regexp_replace (
regexp_substr (mask, ' (\\.)*9 ', 1,11-level)
,' ((\\.) *) (9) ', '\1 ' || substr (num, 11-level, 1)
)
)within group (order by level desc)
from dual
connect by level &lt;=10)
,' \\(.) ', '\1'
)
from m``````

[/spoiler] [spoiler with to_char]

``````with m as (select ' \+\7 \(999 \) 999\-99\-99 ' mask, ' 4959009876 ' num from dual)
select
m.*
,regexp_replace (
(select
listagg (
to_char (
to_date (1000+substr (num, 11-level, 1), ' yyyy ')
,regexp_replace (
regexp_substr (mask, ' (\\.)*9 ', 1,11-level)
,' ((\\.) *) 9 ', ' "\1" y'
)
)
)within group (order by level desc)
from dual
connect by level &lt;=10)
,' \\(.) ', '\1'
)
from m;``````

[/spoiler] [spoiler c utl_lms is much easier]

``````with function format_number (p_mask in varchar2, p_num varchar2) return varchar2
as
begin
return
regexp_replace (
utl_lms.format_message (
regexp_replace (p_mask, ' (\\.)*9 ', \1%s ')
,substr (p_num, 1,1)
,substr (p_num, 2,1)
,substr (p_num, 3,1)
,substr (p_num, 4,1)
,substr (p_num, 5,1)
,substr (p_num, 6,1)
,substr (p_num, 7,1)
,substr (p_num, 8,1)
,substr (p_num, 9,1)
,substr (p_num, 10,1)
)
,' \\(.)'
,'\1'
);
end format_number;
m as (select ' \+\7 \(999 \) 999\-99\-99 ' mask, ' 4959009876 ' num from dual)
select
m.*
from m``````

[/spoiler]

dbms_photoshop wrote:

SY;
I understand rpovarov can sharpens skill with rec with

IMHO such things should be removed generally from basis, let frontend (or what other customer) itself converts. Or rigidly to restrict on an amount of signs that decisions which showed SY and xtender worked. And then to beat feet of any who comes with idea to change an amount of positions.
At me on type jobs "... We consider only numbers of the Russian Federation which have been written down in a format of 10 digits..." Works an internal alarm signal because after preparation of the beautiful and elegant decision with probability of 90 % the format comes input "can be from 8 to 12 digits". Also it is necessary to alter, sometimes completely. It not a pin towards the author of a subject, is simple from experience.

The decision No1 by means of MODEL (for a sample took the code dbms_photoshop).
- Are processed '\9 ' and ' \\' (temporal changeover by "unprintable" characters)
- The arbitrary amount of signs (by itself, number should correspond to a mask)

``````with m (id, mask, phone_num) as
(
select 1, ' 999\-999\-99\-99 ', ' 4959009876 ' from dual
union all
select 2, ' \+\7 \(999 \) 999\-99\-99 \;\1 ', ' 4959009876 ' from dual
union all
select 3, '\9\-\8\-9999999999 \; ', ' 4959009876 ' from dual
union all
select 4, '\9\-\8\-9999999999999 \; ', ' 4959009876123 ' from dual
union all
select 5, ' \\\9\-\8\-9999999 \; ', ' 4959009 ' from dual
union all
select 6, ' Type number and so on: \9\-\8\-9999999 \; ', ' 4959009 ' from dual
)
select
id, mask, phone_num, replace (replace (result, chr (254), ' 9 '), chr (255), ' \') as result
from m
model
partition by (id)
dimension by (0 dummy)
measures (mask, replace (replace (replace (mask, '\9 ', chr (254)), ' \\', chr (255)), ' \') mask_filtered, phone_num, cast (null as varchar2 (240)) result)
rules iterate (1e9) until (substr (phone_num [0], iteration_number+2, 1) is null)
(
result [0] = regexp_replace (decode (iteration_number, 0, mask_filtered [0], result [0]),
' 9 ',
substr (phone_num [0], iteration_number+1, 1),
instr (mask_filtered [0], ' 9 ', 1, iteration_number+1),
1
)
)
order by id;
---------- ----------------------------------------------------------- ------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 999\-999\-99\-99 4959009876 495-900-98-76
2 \+ \7 \(999 \) 999\-99\-99 \;\1 4959009876 +7(495) 900-98-76; 1
3 \9\-\8\-9999999999 \; 4959009876 9-8-4959009876;
4 \9\-\8\-9999999999999 \; 4959009876123 9-8-4959009876123;
5 \\\9\-\8\-9999999 \; 4959009 \9-8-4959009;
6 Types number and so on: \9\-\8\-9999999 \; 4959009 Types number and so on: 9-8-4959009;``````

rpovarov;

``````with m (id, mask, phone_num) as
(
select 1, ' \\9999999 ', ' 4959009 ' from dual
)
select
id, mask, phone_num, replace (replace (result, chr (254), ' 9 '), chr (255), ' \') as result
from m
model
partition by (id)
dimension by (0 dummy)
measures (mask, replace (replace (replace (mask, '\9 ', chr (254)), ' \\', chr (255)), ' \') mask_filtered, phone_num, cast (null as varchar2 (240)) result)
rules iterate (1e9) until (substr (phone_num [0], iteration_number+2, 1) is null)
(
result [0] = regexp_replace (decode (iteration_number, 0, mask_filtered [0], result [0]),
' 9 ',
substr (phone_num [0], iteration_number+1, 1),
instr (mask_filtered [0], ' 9 ', 1, iteration_number+1),
1
)
)
order by id;
ORA-01428: argument ' 0 ' is out of range``````

xtender;
Precisely, thanks. It is necessary to change sequence//and/9.
[spoiler]

``````with m (id, mask, phone_num) as
(
select 1, ' \\9999999 ', ' 4959009 ' from dual
)
select
id, mask, phone_num, replace (replace (result, chr (254), ' 9 '), chr (255), ' \') as result
from m
model
partition by (id)
dimension by (0 dummy)
measures (mask, replace (replace (replace (mask,&gt;&gt;&gt; ' \\', chr (255)), '\9 ', chr (254)), ' \' &lt;&lt;&lt;) mask_filtered, phone_num, cast (null as varchar2 (240)) result)
rules iterate (1e9) until (substr (phone_num [0], iteration_number+2, 1) is null)
(
result [0] = regexp_replace (decode (iteration_number, 0, mask_filtered [0], result [0]),
' 9 ',
substr (phone_num [0], iteration_number+1, 1),
instr (mask_filtered [0], ' 9 ', 1, iteration_number+1),
1
)
)
order by id;