I am trying to build a Regex_Replace workaround to a long and often confusing Replace (Oracle12c)
up vote
-2
down vote
favorite
The select statement goes:
SELECT replace ( replace ( replace
( replace (table.ADDRESS,'Scarborough',' XXScarborough '),
'North York','XXX York '),'Toronto','XXXToronto
'),'ON','NON ') as address,...
oracle oracle12c
edited Nov 10 at 4:22
Kaushik Nayak
16.3k41128
asked Nov 9 at 20:07
ShivCOT
1
add a comment |
up vote
-2
down vote
favorite
The select statement goes:
SELECT replace ( replace ( replace
( replace (table.ADDRESS,'Scarborough',' XXScarborough '),
'North York','XXX York '),'Toronto','XXXToronto
'),'ON','NON ') as address,...
oracle oracle12c
edited Nov 10 at 4:22
Kaushik Nayak
16.3k41128
asked Nov 9 at 20:07
ShivCOT
1
3
It won't be any simpler. REGEX allows you to search for several patterns in a single go (unlike REPLACE), but it still only allows you ONE replacement string; you can't direct it to replace each of four patterns with a corresponding (and different) replacement text.
– mathguy
Nov 9 at 20:12
idownvotedbecau.se/noattempt . Also, you should format your code in your question using the code formatting tools.
– Jack
Nov 9 at 20:13
add a comment |
up vote
-2
down vote
favorite
up vote
-2
down vote
favorite
The select statement goes:
SELECT replace ( replace ( replace
( replace (table.ADDRESS,'Scarborough',' XXScarborough '),
'North York','XXX York '),'Toronto','XXXToronto
'),'ON','NON ') as address,...
oracle oracle12c
edited Nov 10 at 4:22
Kaushik Nayak
16.3k41128
asked Nov 9 at 20:07
ShivCOT
1
The select statement goes:
SELECT replace ( replace ( replace
( replace (table.ADDRESS,'Scarborough',' XXScarborough '),
'North York','XXX York '),'Toronto','XXXToronto
'),'ON','NON ') as address,...
oracle oracle12c
oracle oracle12c
edited Nov 10 at 4:22
Kaushik Nayak
16.3k41128
asked Nov 9 at 20:07
ShivCOT
1
edited Nov 10 at 4:22
Kaushik Nayak
16.3k41128
asked Nov 9 at 20:07
ShivCOT
1
edited Nov 10 at 4:22
Kaushik Nayak
16.3k41128
edited Nov 10 at 4:22
Kaushik Nayak
16.3k41128
edited Nov 10 at 4:22
Kaushik Nayak
16.3k41128
16.3k41128
asked Nov 9 at 20:07
ShivCOT
1
asked Nov 9 at 20:07
ShivCOT
1
asked Nov 9 at 20:07
ShivCOT
1
1
3
It won't be any simpler. REGEX allows you to search for several patterns in a single go (unlike REPLACE), but it still only allows you ONE replacement string; you can't direct it to replace each of four patterns with a corresponding (and different) replacement text.
– mathguy
Nov 9 at 20:12
idownvotedbecau.se/noattempt . Also, you should format your code in your question using the code formatting tools.
– Jack
Nov 9 at 20:13
add a comment |
3
It won't be any simpler. REGEX allows you to search for several patterns in a single go (unlike REPLACE), but it still only allows you ONE replacement string; you can't direct it to replace each of four patterns with a corresponding (and different) replacement text.
– mathguy
Nov 9 at 20:12
idownvotedbecau.se/noattempt . Also, you should format your code in your question using the code formatting tools.
– Jack
Nov 9 at 20:13
3
3
It won't be any simpler. REGEX allows you to search for several patterns in a single go (unlike REPLACE), but it still only allows you ONE replacement string; you can't direct it to replace each of four patterns with a corresponding (and different) replacement text.
– mathguy
Nov 9 at 20:12
It won't be any simpler. REGEX allows you to search for several patterns in a single go (unlike REPLACE), but it still only allows you ONE replacement string; you can't direct it to replace each of four patterns with a corresponding (and different) replacement text.
– mathguy
Nov 9 at 20:12
idownvotedbecau.se/noattempt . Also, you should format your code in your question using the code formatting tools.
– Jack
Nov 9 at 20:13
idownvotedbecau.se/noattempt . Also, you should format your code in your question using the code formatting tools.
– Jack
Nov 9 at 20:13
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
One alternative is to store the pattern and replacement in an associative array, put it in a with clause function. Though it ain't any shorter than your replace, it should definitely be less "confusing".
WITH FUNCTION my_replace (
inp VARCHAR2
) RETURN VARCHAR2 IS
v_out VARCHAR2(1000) := inp;
TYPE v_astype IS
TABLE OF VARCHAR2(40) INDEX BY VARCHAR(40);
v_pat v_astype;
v_idx VARCHAR2(40);
BEGIN
v_pat('Scarborough') := (' XXScarborough ');
v_pat('North York') := ('XXX York ');
v_pat('Toronto') := ('XXXToronto ');
v_pat('ON') := ('NON ');
v_idx := v_pat.first;
WHILE v_idx IS NOT NULL LOOP
v_out := replace(v_out,v_idx,v_pat(v_idx) );
v_idx := v_pat.next(v_idx);
END LOOP;
RETURN v_out;
END;
SELECT my_replace(t.ADDRESS) --,other columns
FROM mytable t;
Demo
answered Nov 10 at 4:32
Kaushik Nayak
16.3k41128
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
One alternative is to store the pattern and replacement in an associative array, put it in a with clause function. Though it ain't any shorter than your replace, it should definitely be less "confusing".
WITH FUNCTION my_replace (
inp VARCHAR2
) RETURN VARCHAR2 IS
v_out VARCHAR2(1000) := inp;
TYPE v_astype IS
TABLE OF VARCHAR2(40) INDEX BY VARCHAR(40);
v_pat v_astype;
v_idx VARCHAR2(40);
BEGIN
v_pat('Scarborough') := (' XXScarborough ');
v_pat('North York') := ('XXX York ');
v_pat('Toronto') := ('XXXToronto ');
v_pat('ON') := ('NON ');
v_idx := v_pat.first;
WHILE v_idx IS NOT NULL LOOP
v_out := replace(v_out,v_idx,v_pat(v_idx) );
v_idx := v_pat.next(v_idx);
END LOOP;
RETURN v_out;
END;
SELECT my_replace(t.ADDRESS) --,other columns
FROM mytable t;
Demo
answered Nov 10 at 4:32
Kaushik Nayak
16.3k41128
add a comment |
up vote
0
down vote
One alternative is to store the pattern and replacement in an associative array, put it in a with clause function. Though it ain't any shorter than your replace, it should definitely be less "confusing".
WITH FUNCTION my_replace (
inp VARCHAR2
) RETURN VARCHAR2 IS
v_out VARCHAR2(1000) := inp;
TYPE v_astype IS
TABLE OF VARCHAR2(40) INDEX BY VARCHAR(40);
v_pat v_astype;
v_idx VARCHAR2(40);
BEGIN
v_pat('Scarborough') := (' XXScarborough ');
v_pat('North York') := ('XXX York ');
v_pat('Toronto') := ('XXXToronto ');
v_pat('ON') := ('NON ');
v_idx := v_pat.first;
WHILE v_idx IS NOT NULL LOOP
v_out := replace(v_out,v_idx,v_pat(v_idx) );
v_idx := v_pat.next(v_idx);
END LOOP;
RETURN v_out;
END;
SELECT my_replace(t.ADDRESS) --,other columns
FROM mytable t;
Demo
answered Nov 10 at 4:32
Kaushik Nayak
16.3k41128
add a comment |
up vote
0
down vote
up vote
0
down vote
One alternative is to store the pattern and replacement in an associative array, put it in a with clause function. Though it ain't any shorter than your replace, it should definitely be less "confusing".
WITH FUNCTION my_replace (
inp VARCHAR2
) RETURN VARCHAR2 IS
v_out VARCHAR2(1000) := inp;
TYPE v_astype IS
TABLE OF VARCHAR2(40) INDEX BY VARCHAR(40);
v_pat v_astype;
v_idx VARCHAR2(40);
BEGIN
v_pat('Scarborough') := (' XXScarborough ');
v_pat('North York') := ('XXX York ');
v_pat('Toronto') := ('XXXToronto ');
v_pat('ON') := ('NON ');
v_idx := v_pat.first;
WHILE v_idx IS NOT NULL LOOP
v_out := replace(v_out,v_idx,v_pat(v_idx) );
v_idx := v_pat.next(v_idx);
END LOOP;
RETURN v_out;
END;
SELECT my_replace(t.ADDRESS) --,other columns
FROM mytable t;
Demo
answered Nov 10 at 4:32
Kaushik Nayak
16.3k41128
One alternative is to store the pattern and replacement in an associative array, put it in a with clause function. Though it ain't any shorter than your replace, it should definitely be less "confusing".
WITH FUNCTION my_replace (
inp VARCHAR2
) RETURN VARCHAR2 IS
v_out VARCHAR2(1000) := inp;
TYPE v_astype IS
TABLE OF VARCHAR2(40) INDEX BY VARCHAR(40);
v_pat v_astype;
v_idx VARCHAR2(40);
BEGIN
v_pat('Scarborough') := (' XXScarborough ');
v_pat('North York') := ('XXX York ');
v_pat('Toronto') := ('XXXToronto ');
v_pat('ON') := ('NON ');
v_idx := v_pat.first;
WHILE v_idx IS NOT NULL LOOP
v_out := replace(v_out,v_idx,v_pat(v_idx) );
v_idx := v_pat.next(v_idx);
END LOOP;
RETURN v_out;
END;
SELECT my_replace(t.ADDRESS) --,other columns
FROM mytable t;
Demo
answered Nov 10 at 4:32
Kaushik Nayak
16.3k41128
edited Nov 10 at 4:39
answered Nov 10 at 4:32
Kaushik Nayak
16.3k41128
answered Nov 10 at 4:32
Kaushik Nayak
16.3k41128
answered Nov 10 at 4:32
Kaushik Nayak
16.3k41128
16.3k41128
add a comment |
add a comment |
draft saved
draft discarded
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53232636%2fi-am-trying-to-build-a-regex-replace-workaround-to-a-long-and-often-confusing-re%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
3
It won't be any simpler. REGEX allows you to search for several patterns in a single go (unlike REPLACE), but it still only allows you ONE replacement string; you can't direct it to replace each of four patterns with a corresponding (and different) replacement text.
– mathguy
Nov 9 at 20:12
idownvotedbecau.se/noattempt . Also, you should format your code in your question using the code formatting tools.
– Jack
Nov 9 at 20:13