Thanks for the question, Thiru.

Asked: November 24, 2003 - 11:55 am UTC

Last updated: March 22, 2017 - 2:01 am UTC

Version: 8.1.7.4

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I was waiting to ask this question for last two days, now only i got this far. So here is my question.
We have column called prmd_title varchar2(512).
It is possible we will get a title with multiple space in between the words.
For example,

PRMD_TITLE
==========
Archivos de biologia y medicina experimentales
Archivos de Psiquiatria
Archivos de pediatria del Uruguay
Archivos dominicanos de pediatria
Archivos espanoles de urologia
Archivos latinoame ricanos de nutricion

But in some queries we need to remove those multiple blanks space to single blank space.

We tried following.
1) create a function that goes through the string removes it. here is the function
CREATE OR REPLACE FUNCTION PQDR2SPC
(PI_INSTR IN VARCHAR2
)
RETURN VARCHAR2
IS
IN_STR varchar2(2000);
OUT_STR varchar2(2000);
BEGIN
IN_STR := pi_inStr;
WHILE instr(IN_STR,' ',1) > 0 LOOP
OUT_STR := replace(IN_STR,' ',' ');
IN_STR := OUT_STR;
END LOOP;
RETURN(IN_STR);
END PQDR2SPC

This takes longtime and affects our performance.

2. We used following to replace, but limitation as you can see it will remove only maximum of five blank space.

select
replace(replace(replace(replace(replace(
upper(decode(prmd_overwrite_pm_name,null,prmd_original_pm_name,
prmd_overwrite_pm_name)),
' ',' '),' ',' '),' ',' '),' ',' '),' ',' ')
from ttl_print_medias

I created the function based index now its performance is ok.But this will have problem if we have six or more space continously.
Is there any generic way to do this??

Thanks for your response.

-Thiru

and Tom said...

well, it'll actually do way way more then 5!

ops$tkyte@ORA920> select
2 replace(replace(replace(replace(replace(data,
3 ' ',' '),' ',' '),' ',' '),' ',' '),' ',' '),
4 length(data)
5 from (select 'x y' data from dual )
6 /

REP LENGTH(DATA)
--- ------------
x y 15

the way you coded it -- turns all 5 blanks into 1. Then, that replaced string is used to turn 4 into 1 and so on.

ops$tkyte@ORA920> select
2 replace(replace(replace(replace(replace(data,
3 ' ',' '),' ',' '),' ',' '),' ',' '),' ',' '),
4 length(data)
5 from (select 'x y' data from dual )
6 /

REP LENGTH(DATA)
--- ------------
x y 53

it'll actually get upto 210 BLANKS!!! add one more replace of 6 to 1 blank and you'll get 1230 blanks!

I brute forced the "proof":

drop table t;

create table t ( data varchar2(4000) );

begin
for i in 1 .. 3998
loop
insert into t values ( 'x' || rpad(' ',i,' ') || 'y' );
end loop;
end;
/
column data2 format a5

select data2, l_data,
decode( lag(l_data) over (order by l_data), l_data-1, null, '<<<====' ) lag_l_data
from (
select
replace( replace(replace(replace(replace(replace(data,
' ',' '),' ',' '),' ',' '),' ',' '),' ',' '),' ',' ') data2,
length(data) l_data
from t
)
where length(data2) = 3
/

just look for the first <<<====== to see where it stops processing...

Rating

(44 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

another way

Paul Moen, November 24, 2003 - 11:09 pm UTC

what about OWA_PATTERN.CHANGE
This package allows for regex (regular expressions) in Oracle 9i, don't know about 8i
not as powerful as perl but probably enough to do this job.

CREATE OR REPLACE FUNCTION PQDR2SPC
(PI_INSTR IN VARCHAR2
)
RETURN VARCHAR2
IS
IN_STR varchar2(2000);
OUT_STR varchar2(2000);
BEGIN
IN_STR := pi_inStr;
OWA_PATTERN.CHANGE(IN_STR,'\s{2,}',' ','g');
RETURN(IN_STR);
END PQDR2SPC

\s{2,} will replace any 2 or more whitespace chars with one whitespace.
This will work for LOBS,CLOBS as well.

There is an article about regex in 10G on oramag...

Have Fun

November 25, 2003 - 7:40 am UTC

it suffers from the same issue the person asking the question wanted to avoid in the first place! they had a fairly optimized plsql function already -- and deemed it "too slow"

to Paul: owa_pattern is slow

msc, November 25, 2003 - 2:37 am UTC

Hi Paul,

owa_pattern would have seriuos impact on performance - it is pure PLSQL and very complex one also. You better use your own replacement function if you do not like replace()-s.

But using several replace()-s in SQL will beat any PLSQL function anyway (create view if you need to hide complexity).

Dividing a string into multiple parts

Arvind, November 25, 2003 - 5:40 am UTC

Dear Tom,

I have problem with spaces. I have column of length 200 and I am dividing into four columns. All column types are of varchar2. Some time later on it is also required to concatenate the string back. I am facing a problem is that after dividing the string i last character happens to be a blank then that blank goes of and while concatinating the two words which were separted by a blank, just conctenates.

November 25, 2003 - 7:52 am UTC

give me an "example", not understanding the issue here.

Thanks.

Thiru Pandian, November 25, 2003 - 10:02 am UTC

THanks Tom. You are always right. I don't think i need to revisit that problem as it can remove 210 blank spaces. I have one more for you,hopefully i am not asking too much.. just follow your advice "if you can do it sql, try to do it in sql" -:).

We form URL based on Author and title and it is possible these attributes will have few special characters.So before we give this URL to web we convert all special character to hexa character. Right now we do this by calling PL/SQL function. Do think is there any better way than this?

Here is our PL/SQL Function.
hexcharacterstring VARCHAR2(100) := '% ;/?:@&=+$,<>#{}|\^[]`"';
FUNCTION convert_type (pi_in_string IN VARCHAR2 ,
pi_convert_type IN VARCHAR2 ,
pi_debug_flag IN NUMBER)
RETURN VARCHAR2 IS

lv_temp_string VARCHAR2(3000);
po_out_string VARCHAR2(3000);
lv_message_grp_id VARCHAR2(15);
lv_function_name VARCHAR2(30);
BEGIN
lv_temp_string := pi_in_string;
FOR LoopPos IN 1..LENGTH(hexcharacterstring)
LOOP
hex_table(LoopPos) := substr(hexcharacterstring,LoopPos, 1);
SELECT REPLACE(lv_temp_string, hex_table(LoopPos) ,lv_reserve_char
|| rawtohex(hex_table(LoopPos)))
INTO po_out_string FROM dual;
lv_temp_string := po_out_string;
END LOOP;
END;

Thanks.
-Thiru.

November 25, 2003 - 12:29 pm UTC

create or replace function urlencode( p_str in varchar2 ) return varchar2
as
l_tmp varchar2(12000);
l_len number default length(p_str);
l_bad varchar2(100) default ' >%}\~];?@&<#{|^[`/:=$+''"' || chr(10);
l_char char(1);
begin
if ( p_str is NULL ) then
return NULL;
end if;

for i in 1 .. l_len loop
l_char := substr(p_str,i,1);
if ( instr( l_bad, l_char ) > 0 )
then
l_tmp := l_tmp || '%' || to_char(ascii(l_char), 'fm0X');
else
l_tmp := l_tmp || l_char;
end if;
end loop;

return l_tmp;
end;
/

is my version (don't select replace -- just assign replace -- in the future!)

Thanks.

Thiru Pandian, November 25, 2003 - 1:36 pm UTC

Thanks Tom. It was helpful.
-Thiru.

Another way of doing the same?

A reader, November 25, 2003 - 4:45 pm UTC

declare
function urlencode(p_str in varchar2) return varchar2 as
l_char varchar2(1);
type t_repl is table of varchar2(10) index by l_char%type;
a_repl t_repl;
l_outstr varchar2(4000) := '';
l_len number := length(p_str);
l_xlate varchar2(100) := ' >%}\~];?@&<#{|^[`/:=$+''"' || chr(10);
begin
if p_str is null then
return null;
end if;
for i in 1 .. length(l_xlate) loop
a_repl(substr(l_xlate,i,1)) := '%'||to_char(ascii(substr(l_xlate,i,1)), 'fm0X');
end loop;
for i in 1 .. length(p_str) loop
l_char := substr(p_str,i,1);
if a_repl.exists(l_char) then
l_outstr := l_outstr || a_repl(l_char);
else
l_outstr := l_outstr || l_char;
end if;
end loop;
return l_outstr;
end;
begin
dbms_output.put_line(urlencode('my simon.com/~uuu'));
end;

November 25, 2003 - 6:39 pm UTC

there are an infinite number of ways to code this, yes.

Dividing a string into multiple parts

Arvind, November 26, 2003 - 10:39 pm UTC

The problem which I have narrated is given below. The example is

I have a columj description of varchar2(100). The data in that column is given below

VLV.BALL, SHEET 54313 2.0 INCH

now I have to divide this decription in four columns as given below
DESC_1 VARCHAR2(22)
DESC_2 VARCHAR2(31)
DESC_3 VARCHAR2(10)
DESC_4 VARCHAR2(37)

If I didvide string in four parts then it takes string
desc_1 = "VLV.BALL, SHEET 54313 "
desc_2 = "2.0 INCH"
desc_3 = null
desc_4 = null

when I concatenate above four string then the result appears to be

VLV.BALL, SHEET 543132.0 INCH

Here the blank space beween 54313 and 2.0 goes off.

this is the wrong result.

I use the statement desc_1||desc_2||desc_3||desc_4 for concatenation.

This problem happens only when after doing substring if the substring has last or first character as blank.

Please tell me the solution for the problem.

Thanks
Regards

November 25, 2003
Reviewer: Arvind from India

Dear Tom,

I have problem with spaces. I have column of length 200 and I am dividing into
four columns. All column types are of varchar2. Some time later on it is also
required to concatenate the string back. I am facing a problem is that after
dividing the string i last character happens to be a blank then that blank goes
of and while concatinating the two words which were separted by a blank, just
conctenates.

Followup:
give me an "example", not understanding the issue here.

November 27, 2003 - 10:31 am UTC

please give me the full example -- you mention substr, but I don't see substr anywhere in there!

something like this:


ops$tkyte@ORA920> select desc_1, desc_2, desc_1||desc_2
  2    from (
  3  select substr(data,1,22) desc_1,
  4         substr(data,23,31) desc_2,
  5             substr(data,54,10) desc_3,
  6             substr(data,64,37) desc_4
  7    from (
  8  select 'VLV.BALL, SHEET 54313 2.0 INCH' data from dual
  9         )
 10         )
 11  /
 
DESC_1                 DESC_2   DESC_1||DESC_2
---------------------- -------- ------------------------------
VLV.BALL, SHEET 54313  2.0 INCH VLV.BALL, SHEET 54313 2.0 INCH


(which shows I don't see what you say I should be seeing!)

replace mulit blanks in single using 3 "replace"

Reeds Lau, November 27, 2003 - 8:49 pm UTC

Hi,

Pls try this :
Note : using '@' is just an example, you may choose any one
from the code page -- chr(n) instead.

select
replace(
replace (
replace('&1',' ',' @'),'@ ',''),' @',' ')
from dual;

09:47:03 DB:ORCL>select
09:47:05 2 replace(
09:47:05 3 replace (
09:47:05 4 replace('&1',' ',' @'),'@ ',''),' @',' ')
09:47:05 5 from dual;
Enter value for 1: REPLACE MULTIPLE BLANKS IN SINGLE

REPLACE(REPLACE(REPLACE('REPLACEMUL
-----------------------------------
REPLACE MULTIPLE BLANKS IN SINGLE

Rgs,
Reeds

November 28, 2003 - 10:19 am UTC

neat approach, as long as @ is not in your string (you must pick a "safe" character)


ops$tkyte@ORA920> select
  2      replace('how         now          brown       cow',
  3       ' ',' @') from dual;
 
REPLACE('HOWNOWBROWNCOW','','@')
------------------------------------------------------------------
how @ @ @ @ @ @ @ @ @now @ @ @ @ @ @ @ @ @ @brown @ @ @ @ @ @ @cow
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> select
  2    replace (
  3      replace('how         now          brown       cow',
  4       ' ',' @'),'@ ','') from dual;
 
REPLACE(REPLACE('HOW
--------------------
how @now @brown @cow
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> select
  2   replace(
  3    replace (
  4      replace('how         now          brown       cow',
  5       ' ',' @'),'@ ',''),' @',' ')
  6  from dual;
 
REPLACE(REPLACE(R
-----------------
how now brown cow
 
ops$tkyte@ORA920>

Dividing a String into Multiple parts

Arvind, November 27, 2003 - 10:28 pm UTC

The example is shown below

select item_desc from item_detail

ITEM_DESC
----------------------------------------------------------------------------
VLV.BALL, SHEET 54313 2.0 INCH
VLV.BALL, SHEET 54413 1.0 INCH
VLV.BALL, SHEET 54413 3.0 INCH
VLV.BALL, SHEET 54413 6.0 INCH

desc items
IT_DESC_1 VARCHAR2(22)
IT_DESC_2 VARCHAR2(31)
IT_DESC_3 VARCHAR2(10)
IT_DESC_4 VARCHAR2(37)

insert into items(it_desc_1, it_desc_2, it_desc_3, it_desc_4)
(select SUBSTR(ITEM_DESC,1,22), SUBSTR(ITEM_DESC,23,31), SUBSTR(ITEM_DESC,54,10),
SUBSTR(ITEM_DESC,64,37) from item_detail)

select it_desc_1, it_desc_2, it_desc_1||it_desc_2 from items

IT_DESC_1 IT_DESC_2 IT_DESC_1||IT_DESC_2
---------------------- ------------------------------- -------------------------------------
VLV.BALL, SHEET 54313 2.0 INCH VLV.BALL, SHEET 543132.0 INCH

VLV.BALL, SHEET 54413 1.0 INCH VLV.BALL, SHEET 544131.0 INCH

VLV.BALL, SHEET 54413 3.0 INCH VLV.BALL, SHEET 544133.0 INCH

VLV.BALL, SHEET 54413 6.0 INCH VLV.BALL, SHEET 544136.0 INCH

The problem which I have narrated is given below. The example is

I have a column description of varchar2(100). The data in that column is given
below

VLV.BALL, SHEET 54313 2.0 INCH

now I have to divide this decription in four columns as given below
DESC_1 VARCHAR2(22)
DESC_2 VARCHAR2(31)
DESC_3 VARCHAR2(10)
DESC_4 VARCHAR2(37)

If I didvide string in four parts then it takes string
desc_1 = "VLV.BALL, SHEET 54313 "
desc_2 = "2.0 INCH"
desc_3 = null
desc_4 = null

when I concatenate above four string then the result appears to be

VLV.BALL, SHEET 543132.0 INCH

Here the blank space beween 54313 and 2.0 goes off.

this is the wrong result.

I use the statement desc_1||desc_2||desc_3||desc_4 for concatenation.

This problem happens only when after doing substring if the substring has last
or first character as blank.

Please tell me the solution for the problem.

Thanks
Regards

ops$tkyte@ORA920> select desc_1, desc_2, desc_1||desc_2
2 from (
3 select substr(data,1,22) desc_1,
4 substr(data,23,31) desc_2,
5 substr(data,54,10) desc_3,
6 substr(data,64,37) desc_4
7 from (
8 select 'VLV.BALL, SHEET 54313 2.0 INCH' data from dual
9 )
10 )
11 /

DESC_1 DESC_2 DESC_1||DESC_2
---------------------- -------- ------------------------------
VLV.BALL, SHEET 54313 2.0 INCH VLV.BALL, SHEET 54313 2.0 INCH

(which shows I don't see what you say I should be seeing!)

November 28, 2003 - 10:26 am UTC

so, like I've said -- please give me an entire test case to reproduce with.  You must be doing SOMETHING DIFFERENT from what you describe.  A simple, small, concise YET 100% COMPLETE sqlplus script!! 

Like this, that shows what you describe does not result in the output you are observing (so there is something else afoot here!!)

ops$tkyte@ORA920> create table item_detail ( item_desc varchar2(255) );
 
Table created.
 
ops$tkyte@ORA920> create table items ( it_desc_1 varchar2(22), it_desc_2 varchar2(31), it_desc_3 varchar2(10), it_desc_4
  2  varchar2(37) );
 
Table created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into item_detail values ( 'VLV.BALL, SHEET 54313 2.0 INCH' );
 
1 row created.
 
ops$tkyte@ORA920> insert into item_detail values ( 'VLV.BALL, SHEET 54413 1.0 INCH' );
 
1 row created.
 
ops$tkyte@ORA920> insert into item_detail values ( 'VLV.BALL, SHEET 54413 3.0 INCH' );
 
1 row created.
 
ops$tkyte@ORA920> insert into item_detail values ( 'VLV.BALL, SHEET 54413 6.0 INCH' );
 
1 row created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into items(it_desc_1, it_desc_2, it_desc_3, it_desc_4)
  2  (select SUBSTR(ITEM_DESC,1,22), SUBSTR(ITEM_DESC,23,31),
  3  SUBSTR(ITEM_DESC,54,10),
  4          SUBSTR(ITEM_DESC,64,37) from item_detail)
  5  /
 
4 rows created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> select it_desc_1, it_desc_2, it_desc_1||it_desc_2 from items ;
 
IT_DESC_1              IT_DESC_2
---------------------- -------------------------------
IT_DESC_1||IT_DESC_2
-----------------------------------------------------
VLV.BALL, SHEET 54313  2.0 INCH
VLV.BALL, SHEET 54313 2.0 INCH
 
VLV.BALL, SHEET 54413  1.0 INCH
VLV.BALL, SHEET 54413 1.0 INCH
 
VLV.BALL, SHEET 54413  3.0 INCH
VLV.BALL, SHEET 54413 3.0 INCH
 
VLV.BALL, SHEET 54413  6.0 INCH
VLV.BALL, SHEET 54413 6.0 INCH
 

I've done this in 716, 8174, 9203 -- each with identical results.

Dividing String The example is shown below

Arvind, November 27, 2003 - 10:38 pm UTC

Please ignore previous one. This gives the complete detail of the problem.

This problem is happening in this style.

I have a form in D2K 5.0

I am selecting the string of length Varchar2(100) from item_detail thru cursor and putting it in database block. Afer saving the data the blanl gets removed.

Thanks

The example is shown below

select item_desc from item_detail

ITEM_DESC
----------------------------------------------------------------------------
VLV.BALL, SHEET 54313 2.0 INCH
VLV.BALL, SHEET 54413 1.0 INCH
VLV.BALL, SHEET 54413 3.0 INCH
VLV.BALL, SHEET 54413 6.0 INCH

desc items
IT_DESC_1 VARCHAR2(22)
IT_DESC_2 VARCHAR2(31)
IT_DESC_3 VARCHAR2(10)
IT_DESC_4 VARCHAR2(37)

insert into items(it_desc_1, it_desc_2, it_desc_3, it_desc_4)
(select SUBSTR(ITEM_DESC,1,22), SUBSTR(ITEM_DESC,23,31),
SUBSTR(ITEM_DESC,54,10),
SUBSTR(ITEM_DESC,64,37) from item_detail)

select it_desc_1, it_desc_2, it_desc_1||it_desc_2 from items

IT_DESC_1 IT_DESC_2 IT_DESC_1||IT_DESC_2
---------------------- -------------------------------
-------------------------------------
VLV.BALL, SHEET 54313 2.0 INCH VLV.BALL, SHEET 543132.0
INCH

VLV.BALL, SHEET 54413 1.0 INCH VLV.BALL, SHEET 544131.0
INCH

VLV.BALL, SHEET 54413 3.0 INCH VLV.BALL, SHEET 544133.0
INCH

VLV.BALL, SHEET 54413 6.0 INCH VLV.BALL, SHEET 544136.0
INCH

The problem which I have narrated is given below. The example is

I have a column description of varchar2(100). The data in that column is given
below

VLV.BALL, SHEET 54313 2.0 INCH

now I have to divide this decription in four columns as given below
DESC_1 VARCHAR2(22)
DESC_2 VARCHAR2(31)
DESC_3 VARCHAR2(10)
DESC_4 VARCHAR2(37)

If I didvide string in four parts then it takes string
desc_1 = "VLV.BALL, SHEET 54313 "
desc_2 = "2.0 INCH"
desc_3 = null
desc_4 = null

when I concatenate above four string then the result appears to be

VLV.BALL, SHEET 543132.0 INCH

Here the blank space beween 54313 and 2.0 goes off.

this is the wrong result.

I use the statement desc_1||desc_2||desc_3||desc_4 for concatenation.

This problem happens only when after doing substring if the substring has last
or first character as blank.

Please tell me the solution for the problem.

Thanks
Regards

ops$tkyte@ORA920> select desc_1, desc_2, desc_1||desc_2
2 from (
3 select substr(data,1,22) desc_1,
4 substr(data,23,31) desc_2,
5 substr(data,54,10) desc_3,
6 substr(data,64,37) desc_4
7 from (
8 select 'VLV.BALL, SHEET 54313 2.0 INCH' data from dual
9 )
10 )
11 /

DESC_1 DESC_2 DESC_1||DESC_2
---------------------- -------- ------------------------------
VLV.BALL, SHEET 54313 2.0 INCH VLV.BALL, SHEET 54313 2.0 INCH

(which shows I don't see what you say I should be seeing!)

November 28, 2003 - 10:28 am UTC

if you want a fixed width field -- you can use CHAR in the database (preserves trailing blanks, forms is doing blank trimming)

or, you can use RPAD in your concatenate

rpad( desc_1, 22 ) || rpad( desc_2, 31 ) .....

begs recursion

A reader, December 02, 2003 - 9:51 am UTC

The replacement of multiple blanks or other character begs for a recursive solution (see below). Be very careful when writing these types of functions ;-)

DECLARE
n integer;
myCHAR char(1) := 'aa'; --run with various values here
--I'll name this foo and let you come up with your own name
function foo(str in varchar2, search_str in varchar2)
return varchar2
as
myConstant constant integer := 255; --made this a constant to make testing easier/safer; adjust to need
begin
if str is null then --just get this out of the way
return null;
elsif instr(str, search_str || search_str) = 0 then --we have no repeating values of search_str
return str;
--the below is here for efficiency only; remove if not doing very long strings of repeating characters
elsif instr(str, rpad(search_str, myConstant, search_str)) > 0 then
return foo(replace(str, rpad(search_str, myConstant, search_str) , search_str), search_str);
else --replace search_str || search_str with search_str and try again recursively
return foo(replace(str, search_str || search_str, search_str), search_str);
end if;
end;

begin
dbms_output.put_line('---begin---');
for n in 1..2000 loop --loop to your heart's delight
if foo(rpad(myChar, n, myChar) , myChar) = myChar or myChar is null then
null;
else
dbms_output.put_line('#' || foo(rpad(myChar, n, myChar) , myChar) || '#');
end if;
end loop;
--test null
dbms_output.put_line(nvl(foo(null , myChar), 'NULL'));
dbms_output.put_line('---end---');
end;

Of course,we could make the above more robust, but I'll leave that to individual needs.

December 02, 2003 - 10:52 am UTC

I don't know, I found this solution:

select 
 replace(
  replace (
    replace('&1',' ',' @'),'@ ',''),' @',' ')
from dual;

to be really "elegant" (not to mention terse, fast, and 99.9999% complete -- that '@' part isn't too much of a problem, normally, chr(0) will fill the bill as a "safe character".


but a shorter "iterative" solution is

ops$tkyte@ORA920> create or replace function multi_replace( p_str in varchar2 ) return varchar2
  2  is
  3          l_str long := p_str;
  4          l_ret long;
  5  begin
  6          if ( l_str is null ) then return null; end if;
  7
  8          loop
  9                  l_ret := replace( l_str, '  ', ' ' );
 10                  exit when (l_ret = l_str);
 11                  l_str := l_ret;
 12          end loop;
 13          return l_ret;
 14  end;
 15  /
 
Function created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> select multi_replace
  2  ( 'how        now       brown        cow' ),
  3         replace( replace( replace( 'how        now       brown        cow', ' ', ' @'), '@ ', ''),' @', ' ')
  4    from dual;
 
MULTI_REPLACE('HOWNOWBROWNCOW')
-----------------------------------------------------------------------------------------------------------------------------------
REPLACE(REPLACE(R
-----------------
how now brown cow
how now brown cow


but I would really want to use the replace*3:

 

select multi_replace
( 'how        now       brown        cow' )
from t
                                                                                                                   
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.03          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     2110      1.70       2.15         49       2161          0       31630
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2112      1.70       2.19         49       2161          0       31630
                                                                                                                   
                                                                                                                   
select
       replace( replace( replace( 'how        now       brown        cow', ' ', ' @'), '@ ', ''),' @', ' ')
  from t
                                                                                                                   
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     2110      0.28       0.27          0       2161          0       31630
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2112      0.28       0.30          0       2161          0       31630

The three-replace solution is really nifty !

Alan, December 02, 2003 - 12:17 pm UTC

The three-replace solution is really nifty !

You can actually use any string instead of a single char

Dirk, December 03, 2003 - 10:57 am UTC

> neat approach, as long as @ is not in your string (you must pick a "safe" character)
SQL> select 
  2   replace(
  3    replace (
  4      replace('&1',' ',' @#§'),'@#§ ',''),' @#§',' ')
  5  from dual;
Voer waarde voor 1 in: @how         now#          brown§       cow§#@
oud   4:     replace('&1',' ',' @#§'),'@#§ ',''),' @#§',' ')
nieuw   4:     replace('@how         now#          brown§       cow§#@',' ',' @#§'),'@#§ ',''),' @#§',' ')
>>>

REPLACE(REPLACE(REPLACE
-----------------------
@how now# brown§ cow§#@

SQL>

December 03, 2003 - 11:07 am UTC

still, gotta pick a string then :)

I don't think this is a showstopper problem at all -- i really liked the solution totally. very neat.

Another variation

Kannan, December 05, 2003 - 11:15 am UTC

I came up with this function to replace repeated contiguous characters. I am using dynamic sql because of the use of analytic function and the oracle version is 8i.
FUNCTION Replace_Repeated_Char (
pi2_InputString IN VARCHAR2
) RETURN VARCHAR2 IS
TYPE refcur IS REF CURSOR;
vl_rc refcur;
vl2_sql VARCHAR2(2000);
vl2_final_string VARCHAR2(4000);
vln_rnum PLS_INTEGER;
vl2_pattern VARCHAR2(4000);
vl2_single_char VARCHAR2(4000);

BEGIN

vl2_final_string := pi2_InputString;
vl2_sql := '
SELECT rnum, pattern, single_char
FROM
(
SELECT rnum, single_char,
DECODE( LEAD(single_char) OVER( ORDER BY rnum ), single_char, single_char || single_char, NULL ) pattern
FROM (
SELECT rnum, SUBSTR(:vl2_final_string, rnum, 1) single_char
FROM (
SELECT rownum rnum
FROM ALL_OBJECTS
WHERE rownum < LENGTH(:vl2_final_string ) + 1
)
ORDER BY rnum
)
ORDER BY rnum
)
WHERE pattern IS NOT NULL
ORDER BY rnum ';

OPEN vl_rc FOR vl2_sql USING vl2_final_string, vl2_final_string;
LOOP
FETCH vl_rc INTO vln_rnum, vl2_pattern, vl2_single_char;
EXIT WHEN vl_rc%NOTFOUND;
vl2_final_string := REPLACE(vl2_final_string, vl2_pattern, vl2_single_char );
END LOOP;
CLOSE vl_rc;
RETURN vl2_final_string;
END Replace_Repeated_Char;
/

SELECT Replace_Repeated_Char ( 'NNNNEW YO RRRKKKK' ) from dual;

Result:
NEW YO RK

Regular Expressions Rock!

Tyler D. Muth, November 29, 2004 - 10:07 pm UTC

Now, I can't take a bit of credit for this as it's straight from the doc, but reading this thread reminded me of it. This is a case where regexp_replace (10g only) really shines:

select regexp_replace('abc def ghi ','( ){2,}','\1') a
from dual

On a related note, if you haven't tried Regex Coach, you should:
</code> http://www.weitz.de/regex-coach/ <code>

Basically it lets you type in a text string in one box and a regular expression in another. As you type the regular expression, it dynamically highlights the portion of the text string that the expression corresponds to. In short, it's istantanious feedback while building your expression.

Tyler

Multiple ocurences of strings

VA, July 07, 2005 - 9:53 pm UTC

How can this 3-replace technique be modified to "squeeze" successive occurences of a string into one? If I have a string containing HTML linebreak tags like

abc xyz 123 567

I want to get

abc xyz 123 567

Anyone? Thanks

A reader, July 07, 2005 - 9:58 pm UTC

Doh, the same technique can be applied

select replace(replace(replace('&1',' ',' @'),'@ ',''),' @',' ') from dual;

Sorry!

the magic of primes :-)

Connor, August 02, 2005 - 2:36 am UTC

select
 replace(
  replace(
   replace(
    replace(
     replace(
      replace(
       replace(data,
'                 ',' '),   -- 17 spaces
'             ',' '),       -- 13 spaces
'           ',' '),         -- 11 spaces
'       ',' '),             --  7 spaces
'     ',' '),               --  5 spaces
'   ',' '),                 --  3 spaces
'  ',' ')                   --  2 spaces
from table

handles all the spaces up to 3998 (as per your benchmark script).  The replace with '@' version (silently) stops at 2000 embedded spaces within SQL due to the 4000 char limit.

Ditto with PL/SQL

SQL> set serverout on
SQL> declare
  2    data varchar2(32767);
  3  begin
  4    for i in 1 .. 32765 loop
  5      data := 'x' || rpad(' ',i,' ') || 'y';
  6      if length(
  7   replace(
  8    replace(
  9     replace(
 10      replace(
 11       replace(
 12        replace(
 13         replace(data,
 14  '                 ',' '),
 15  '             ',' '),
 16  '           ',' '),
 17  '       ',' '),
 18  '     ',' '),
 19  '   ',' '),
 20  '  ',' ')
 21  ) > 3 then
 22     dbms_output.put_line(i);
 23  end if;
 24  end loop;
 25  end;
 26  /

PL/SQL procedure successfully completed.

August 02, 2005 - 7:34 am UTC

and it looks pretty too :)

The power of recursion?

Peter Crump, November 01, 2005 - 11:31 am UTC

CREATE OR REPLACE FUNCTION single_spacer (
p_string IN VARCHAR2)
RETURN VARCHAR2
IS
result VARCHAR2(4000);
BEGIN
result := REPLACE (p_string,' ',' ');
IF INSTR(result,' ') = 0
THEN
RETURN (result);
ELSE
RETURN (single_spacer(result));
END IF;
END single_spacer;

Then:

SELECT single_spacer('The Rain In Spain's a Pain')
FROM DUAL;

Works for me :-)

Recursion...

Peter Crump, November 02, 2005 - 4:41 am UTC

Maybe I should add that this is not a terribly efficient solution, just neat and tidy :-)

sagsag, January 19, 2006 - 3:32 pm UTC

a simpler way to do it would be

select replace
(replace
(replace
('test testing tested',' ','}{'),'{}',''),'}{',' ') from dual

1. replaceing all spaces with reversed curly brackets "}{"
2. replace all normal brackets with nulls
3 . replace all revered barckets with 1 space

January 19, 2006 - 4:53 pm UTC

assuming such characters are not valid in your string... (meaning, this can be unsafe)

why prime number

Nopparat V., January 19, 2006 - 10:18 pm UTC

select
 replace(
  replace(
   replace(
    replace(
     replace(
      replace(data,
'         ',' ')            --  9 spaces
'                     ',' '),   -- 21 spaces
'      ',' '),              --  6 spaces
'    ',' '),                --  4 spaces
'   ',' '),                 --  3 spaces
'  ',' ')                   --  2 spaces
from table

can remove 4085 spaces with only 6 replace
and if we use 88 spaces instead of 9 in the most inner replace, then we can remove more than 32765 spaces.
I think the set of number is not related to the magic prime number.

I use this function to find out how many spaces they can remove :
create or replace function max_delsp(
  a1  number default 0,
  a2  number default 0,
  a3  number default 0,
  a4  number default 0,
  a5  number default 0,
  a6  number default 0,
  a7  number default 0
) return number is
  data varchar2(32767);
begin
  for i in 1..32765 loop
    data := 'x'||rpad(' ',i,' ')||'y' ;
    if length (
         replace (
           replace (
             replace (
               replace (
                 replace (
                   replace (
                     replace (
                       data
                     ,rpad(' ',a7,' '),' ')
                   ,rpad(' ',a6,' '),' ')
                 ,rpad(' ',a5,' '),' ')
               ,rpad(' ',a4,' '),' ')
             ,rpad(' ',a3,' '),' ')
           ,rpad(' ',a2,' '),' ')
         ,rpad(' ',a1,' '),' ')
       ) > 3 then
      return i ;
    end if ;
  end loop ;
  return 32765 ;
end ;

SQL> select max_delsp(2,3,4,6,21,88) from dual ;

MAX_DELSP(2,3,4,6,21,88)
------------------------
                   32765

SQL> select max_delsp(2,3,4,6,21,9) from dual ;

MAX_DELSP(2,3,4,6,21,9)
-----------------------
                   4085

However I think the magic using of 3 replaces is the real smart thing.

Another way

Michel Cadot, January 23, 2006 - 3:35 am UTC

Follow 2 queries following another way to do it.

SQL> select id, title from t order by id;
        ID TITLE
---------- ----------------------------------------------------------
         1 Archivos de   biologia      y medicina experimentales
         2 Archivos de     Psiquiatria
         3 Archivos de pediatria    del Uruguay
         4 Archivos dominicanos de pediatria
         5 Archivos espanoles   de urologia
         6 Archivos latinoame   ricanos de nutricion

6 rows selected.

SQL> select id, 
  2         max(substr(sys_connect_by_path(part,' '),2)) title
  3  from ( select id, substr(title, i+1, next_i-i-1) part,
  4                row_number () over (partition by id order by i) curr,
  5                row_number () over (partition by id order by i)-1 prev
  6         from ( select distinct id, title, level lvl,
  7                       instr(title, ' ', 1, level) i,
  8                       instr(title, ' ', 1, level+1) next_i
  9                from (select id, ' '||title||' ' title from t)
 10                connect by id = prior id
 11                    and level < 
 12                          length(title)-length(translate(title,'a ','a'))
 13                    and prior dbms_random.value is not null
 14              )
 15         where next_i > i + 1
 16       )
 17  connect by prior curr = prev and prior id = id
 18  start with curr = 1
 19  group by id
 20  order by id
 21  /
        ID TITLE
---------- ----------------------------------------------------------
         1 Archivos de biologia y medicina experimentales
         2 Archivos de Psiquiatria
         3 Archivos de pediatria del Uruguay
         4 Archivos dominicanos de pediatria
         5 Archivos espanoles de urologia
         6 Archivos latinoame ricanos de nutricion

6 rows selected.

SQL> with mx as 
  2         ( select /*+ MATERIALIZE */ 
  3                  max(length(title)-length(translate(title,'a ','a')))+1 mx 
  4           from t )
  5  select id, 
  6         max(substr(sys_connect_by_path(part,' '),2)) title
  7  from ( select id,
  8                substr(title, 
  9                       instr(title,' ',1,rn)+1,
 10                       instr(title,' ',1,rn+1)-instr(title,' ',1,rn)-1) part,
 11                row_number () over (partition by id order by rn) curr,
 12                row_number () over (partition by id order by rn)-1 prev
 13         from ( select id, title, rn
 14                from (select id, ' '||title||' ' title from t),
 15                     (select rownum rn from dual, mx connect by level <= mx)
 16              )
 17         where instr(title,' ',1,rn+1) > instr(title,' ',1,rn) + 1
 18       )
 19  connect by prior curr = prev and prior id = id
 20  start with curr = 1
 21  group by id
 22  order by id
 23  /
        ID TITLE
---------- ----------------------------------------------------------
         1 Archivos de biologia y medicina experimentales
         2 Archivos de Psiquiatria
         3 Archivos de pediatria del Uruguay
         4 Archivos dominicanos de pediatria
         5 Archivos espanoles de urologia
         6 Archivos latinoame ricanos de nutricion

6 rows selected.

Regards
Michel

RPad and extended char's

A reader, January 25, 2006 - 3:32 pm UTC

Tom,

I have a chinese character stored in the DB. The chinese character is stored fine in the DB and also displays well both in iSQL and the J2EE application. When we run the SQL below with RPAD, the numbers don't seem to add up.

DB characterset - UTF8

Select
customer_name cName
, dump(customer_name) dump_CName
, length(customer_name) Len_CName
, lengthb(customer_name) Bytes_CName
, rpad(customer_name, 1000, ' ') Padded_CName
, length(rpad(customer_name, 1000, ' ')) Len_Padded_CName
, lengthb(rpad(customer_name, 1000, ' ')) Bytes_Padded_CName
from hdr a
where a.no = 71483148;

--Values displayed
cName = ä¸å½å±äº§ä¸»ä¹éå¹´å¢ä¸å¤®å§åä¼
dump_CName = Typ=1 Len=42: 228,184,173,229,155,189,229,133,177,228,186,167,228,184,187,228,185,137,233,157,146,229,185,180,229,155,162,228,184,173,229,164,174,229,167,148,229,145,152,228,188,154
Len_CName = 14
Bytes_CName = 42
Padded_CName = ä¸å½å±äº§ä¸»ä¹éå¹´å¢ä¸å¤®å§åä¼
Len_Padded_CName = 986
Bytes_Padded_CName = 1014

My questions
1) Shouldn't Len_Padded_CName be 1000 instead of 986?
2) Shouldn't Bytes_Padded_CName be equal to 42 + 986 = 1028 instead of 1014?
42 = length in bytes of the 14 chinese characters present in the DB.
986 = Padding 1000 characters, since 14 characters already present, remaining is 1000 - 14 = 986 and since each empty string occupies 1 byte, total is 986.

The basis of my explanation is based on the the example below in Oracle9i SQL Reference, Release 2 (9.2), Part Number A96540-02 for the RPAD function.

<quote>
The following example right-pads a name with the letters "ab" until it is 12 characters long:

SELECT RPAD('MORRISON',12,'ab') "RPAD example"
FROM DUAL;

RPAD example
-----------------
MORRISONabab

</quote>

SELECT RPAD('MORRISON',12,'ab') Name
, length(RPAD('MORRISON',12,'ab')) Len_RPad_Name
, lengthb(RPAD('MORRISON',12,'ab')) Bytes_RPAD_Name
FROM DUAL;

NAME LEN_RPAD_NAME BYTES_RPAD_NAME
------------ ------------- ---------------
MORRISONabab 12 12

Would appreciate it if you can let me know what I am missing here.

Thank you

string pattern

Jdam, February 25, 2007 - 11:07 am UTC

Tom,

I want to write a query to get the different pattern of product code for example

The product code are:

ABC0001 -- Pattern at the begining
ABC28
ABCIOS3

PELIGWEXSZ -- Pattern at the begining
PELIGTTT

TVIOX23CATY -- Pattern at the begining and at the end
TV30CATV

CASA3432XEROSA -- Pattern at the begining and at the end
CASAWQWZWCASWARAROSA

IUW23ZZZ -- Patern at the end
UJZ123232ZZZ

UROJO -- Patern at the end
JNJXROJO

and I want the following output

ABC -- Pattern at the begining
ABC0001
ABC28
ABCIOS3

PELI -- Pattern at the begining

PELIGWEXSZ
PELIGTTT

TV**CATV -- Pattern at the begining and at the end

TVIOX23CATY
TV30CATV

CASA**ROSA -- Pattern at the begining and at the end

CASA3432XEROSA
CASAWQWZWCASWARAROSA

**ZZZ -- Patern at the end

IUW23ZZZ
UJZ123232ZZZ

**ROJO -- Patern at the end

UROJO
JNJXROJO

February 26, 2007 - 1:26 pm UTC

well, i guess i can say this safely:

this makes no sense.

LIKE

A reader, February 26, 2007 - 1:44 pm UTC

Seems he just needs pointing to the documentation for the "LIKE" operator.

February 26, 2007 - 3:30 pm UTC

tell me how you derived that bit of wisdom from this detailed specification?

ABC0001 -- Pattern at the begining
ABC28
ABCIOS3

PELIGWEXSZ -- Pattern at the begining
PELIGTTT

TVIOX23CATY -- Pattern at the begining and at the end
TV30CATV

RE: SQL for string pattern

Frank Zhou, February 26, 2007 - 3:08 pm UTC

Hi Jdam,

Here is a SQL solution for the string pattern

Frank

SQL>
SQL> select max(begin) begin_pattern, max(end) end_pattern
2 from
3 (select max(str) begin, to_char(null) end
4 from
5 (select distinct SUBSTR(old_str, 1, LEVEL ) str,
6 count(*) over (partition by SUBSTR(old_str, 1, LEVEL ) ) ct_str,
7 count(distinct old_str ) over ( ) cnt
8 from
9 (select 'CASA3432XEROSA' old_str from dual
10 union all
11 select 'CASAWQWZWCASWARAROSA' old_str from dual
12 )
13 connect by prior old_str =old_str
14 AND level < length(old_str)
15 AND PRIOR dbms_random.string ('p', 10) IS NOT NULL
16 )
17 where ct_str = cnt

18 UNION ALL

19 select to_char(null), reverse(max(str))
20 from
21 (select distinct SUBSTR(old_str, 1, LEVEL ) str,
22 count(*) over (partition by SUBSTR(old_str, 1, LEVEL ) ) ct_str,
23 count(distinct old_str ) over ( ) cnt
24 from
25 (select reverse( 'CASA3432XEROSA') old_str from dual
26 union all
27 select reverse('CASAWQWZWCASWARAROSA') old_str from dual
28 )
29 connect by prior old_str = old_str
30 AND level < length(old_str)
31 AND PRIOR dbms_random.string ('p', 10) IS NOT NULL
32 )
33 where ct_str = cnt
34 );

BEGIN_PATTERN END_PATTERN
-------------------- --------------------
CASA ROSA

SQL>

String pattern

Jdam, February 26, 2007 - 8:51 pm UTC

Tom,

These are not the real data, it is just an example, the real data come from and old system (non oracle ) with no validation, and I have to analyze the product code.

I identified 3 types of pattern

1. pattern at the beginning
2. pattern at the beginning and at the end
3. and pattern at the end

And a want to display the different pattern and all the rows that match each pattern,
the problem is the patter could be 1 to n character.

February 27, 2007 - 10:33 am UTC

this does not a specification make.

pretend I was your mom and you were trying to explain to her what needed to be done.

Alexander the ok, March 02, 2007 - 11:26 am UTC

I'm having a total brain fart this morning. I just want to get the last 4 characters in a string.

eg given 0123456789

i need 6789

I'm ashamed to even have to ask this i don't know what's wrong with me.

Alexander the ok, March 02, 2007 - 1:03 pm UTC

Nevermind my senses came back to me.

Replace

Krish, May 09, 2007 - 1:31 am UTC

Hi Tom,

Don't know if this is the right place to ask you this question.

we are using Oracle 9i,in one of our tables some how the name column got appended (example given below) with multiples of the last alphabet (in the name).

Example :

In the emp table the data in the ename column some how got appended and the data is being shown in the following way..

EMPNO ENAME                 

7369 SMITHHHHHHHHHHHH         
7499 ALLENNNNNNNNNNNNNNN         
7521 WARDDDDDDDDD                 
7566 JONESSSSSSSSSS                 
7654 MARTINNNNNNNNNN                 
7698 BLAKEEEE                 
7782 CLARKKKKKKKK                 
7788 SCOTTTTTTTTTT                  
7839 KINGGGG                         
7844 TURNERRRRRRRR                  
7876 ADAMSSSSSSS        
7900 JAMESSSSSSSS        
7902 FORDDDDD        
7934 MILLERRRRRRR

I understand this really doesn't make sense but I have 1 million records and am unable to find a solution.

Would appreciate your kind suggestion.

Thanks in advance.

May 11, 2007 - 9:26 am UTC

the data isn't being shown in that way.

that is your data.

now, if there is a fixed number of characters (eg; they all have 5 extra) then update set c = substr( c, 1, length(c)-5 )

if they do not have a fixed number of extra characters, then I would say "you are hosed"

for you cannot tell the difference between:

hardee
harde

does that first ename have extra characters or not?

If you can describe the logic that needs to be applied to your data - we can do the sql.

if you cannot, that is where you HAVE to start.

more elegant regex to use

Andrey, October 15, 2007 - 9:54 am UTC

Here is more useful regex for 10g I use to remove excessive blanks from string, It performs a complete normalization of a string, i.e. besides of replacing blanks inside string it removes leading and trailing blanks as well:
Working example for 10g:
select regexp_replace(' abc def ghi3 ','^ +| +$|( )+','\1') a from dual

how to replace commas with numbers

Chan, August 15, 2008 - 9:51 am UTC

Hi Tom,
I have similar question on replace function.
Wanted to replace all the commans with sequence numbers.

My original data is like this.

SQL> select * from  func_test;

       NO ERRMSG
--------- ------------------------------
        101 ,abcd, sadasdj, asdsad
        102 ,sajdh,dsad, sdfsdf,s sddsf,df

my desired results should be something like this.

SQL> select * from  func_test;

       NO ERRMSG
--------- --------------------------------------
        101  1.abcd 2.sadasdj 3.asdsad 
        102 1.sajdh 2.dsad 3.sdfsdf 4.s sddsf 5.df


Which means.. when ever there is a comma, I need a new number there.

in Oracle 9i R2

Rajeshwaran, Jeyabal, November 27, 2008 - 1:48 am UTC

Hi Chan,
Here is one of the solution from oracle 9i.
CREATE TABLE func_test(x NUMBER,y VARCHAR2(4000));

INSERT INTO func_test VALUES (101,',abcd, sadasdj, asdsad');
INSERT INTO func_test VALUES (102,',sajdh,dsad, sdfsdf,s sddsf,df');

COMMIT;

scott@AFRSQA> SELECT REPLACE(SYS_CONNECT_BY_PATH(ROW_NUM
2 ||'.'
3 ||STR, ', '), ',') AS FINAL_RESULT
4 FROM
5 (SELECT X ,
6 STR ,
7 ROW_NUMBER() OVER(PARTITION BY X ORDER BY STR) AS ROW_NUM,
8 COUNT(*) OVER(PARTITION BY X) AS CNT
9 FROM
10 (SELECT X ,
11 LEVEL LVL ,
12 SUBSTR(Y, INSTR(Y, ',', 1, LEVEL)+1, INSTR(Y, ',', 1, LEVEL+1) - INSTR(Y, ',', 1, LEVEL)-1) STR,
13 LEAD(LEVEL, 1, 0) OVER(PARTITION BY X ORDER BY LEVEL) LEAD
14 FROM
15 (SELECT X,
16 ','
17 ||Y
18 ||',' AS Y
19 FROM Func_test
20 ) CONNECT BY INSTR(Y, ',', 1, LEVEL) > 0
21 )
22 WHERE STR IS NOT NULL
23 AND LVL != LEAD
24 )
25 WHERE ROW_NUM = CNT START
26 WITH ROW_NUM = 1 CONNECT BY PRIOR ROW_NUM = ROW_NUM - 1
27 AND PRIOR X = X
28 /

FINAL_RESULT
--------------------------------------------------------------------------------------------------------------------
1. asdsad 2. sadasdj 3.abcd
1. sdfsdf 2.df 3.dsad 4.s sddsf 5.sajdh

in Oracle 9i R2

Rajeshwaran, Jeyabal, December 03, 2008 - 1:14 pm UTC

Hi Chan,
If you are concerned with the exact same order. Here is the one.
scott@AFRSQA> SELECT X,REPLACE(SYS_CONNECT_BY_PATH(ROW_NUM
2 ||'.'
3 ||STR, ', '), ',') AS FINAL_RESULT
4 FROM
5 (SELECT X ,LVL ,
6 STR ,
7 ROW_NUMBER() OVER(PARTITION BY X ORDER BY LVL) AS ROW_NUM,
8 COUNT(*) OVER(PARTITION BY X) AS CNT
9 FROM
10 (SELECT X ,
11 LEVEL LVL ,
12 SUBSTR(Y, INSTR(Y, ',', 1, LEVEL)+1, INSTR(Y, ',', 1, LEVEL+1) - INSTR(Y, ',', 1, LEVEL)-1) STR,
13 LEAD(LEVEL, 1, 0) OVER(PARTITION BY X ORDER BY LEVEL) LEAD
14 FROM
15 (SELECT X,
16 ','
17 ||Y
18 ||',' AS Y
19 FROM Func_test
20 ) CONNECT BY INSTR(Y, ',', 1, LEVEL) > 0
21 )
22 WHERE STR IS NOT NULL
23 AND LVL != LEAD
24 ORDER BY X,LVL
25 )
26 WHERE ROW_NUM = CNT START
27 WITH ROW_NUM = 1 CONNECT BY PRIOR ROW_NUM = ROW_NUM - 1
28 AND PRIOR X = X
29 /
X FINAL_RESULT
---------------------- ----------------------------------------------
101 1.abcd 2. sadasdj 3. asdsad
102 1.sajdh 2.dsad 3. sdfsdf 4.s sddsf 5.df

How this can be achieved in Oracle 10g?

Rajeshwaran, Jeyabal, February 13, 2009 - 2:01 am UTC

Tom,
Just before a month we upgraded our application from Oracle 9i to Oracle 10g. I have started learning the Oracle 10g features. I am woundering is there any Specific
Oracle 10g features ( like Regular expression , SQL Model Clause) to re-write the abouve Query? If yes, Can you please share me?

Using Regular Expression in - 10G

Rajeshwaran, Jeyabal, November 27, 2009 - 3:05 am UTC

scott@IRADSDB> SELECT x,REPLACE(SYS_CONNECT_BY_PATH(rnum||'.'||reg_y||' ',','),',') AS FINAL_RESULT
2 FROM (
3 SELECT x,
4 lvl,
5 reg_y,
6 row_number() over(PARTITION BY x ORDER BY lvl) AS rnum,
7 COUNT(*) OVER(PARTITION BY x ) AS CNT
8 FROM
9 (SELECT x,
10 y,
11 regexp_substr(y, '[^,]+', 1, LEVEL) AS
12 reg_y,
13 LEVEL lvl,
14 lag(LEVEL, 1, 0) over(PARTITION BY x
15 ORDER BY LEVEL) AS
16 lg_lvl
17 FROM func_test CONNECT BY LEVEL <=(LENGTH(regexp_replace(y, '[^,]')) + 1))
18 WHERE lvl != lg_lvl AND reg_y IS NOT NULL
19 )
20 WHERE rnum = cnt
21 START WITH rnum =1
22 CONNECT BY PRIOR rnum = rnum -1 AND PRIOR x = x;

X FINAL_RESULT
---------- ----------------------------------------
101 1.abcd 2. sadasdj 3. asdsad
102 1.sajdh 2.dsad 3. sdfsdf 4.s sddsf 5.df

Remove blank or others version earlier 10g

Robson Rozati, February 24, 2010 - 10:48 am UTC

A simple function...

create or replace function stripDup( pstr varchar2, pdup varchar2 default ' ') return varchar2 is
vret varchar2(32767) := pstr;
begin
loop
vret := replace( vret, pdup || pdup, pdup);
exit when instr( vret, pdup || pdup ) = 0;
end loop;
return vret;
end;
/

select stripDup( 'a b c d e f g h') from dual

select stripDup( 'a..b...c....d......e........f..............g...............................h','.') from dual

March 01, 2010 - 8:59 am UTC

I still like this better

select
replace(
replace (
replace('&1',' ',' @'),'@ ',''),' @',' ')
from dual;

see above for a discussion

Answer for question posted above by "Krish from Bangalore,India" on May 9, 2007

Logan Palanisamy, March 01, 2010 - 4:54 pm UTC


SQL>select str, regexp_replace(str, '([A-Z])(\1)*$', '\1') new_str FROM T;

STR                            NEW_STR
------------------------------ ----------
SMITHHHHHHHHHHHH               SMITH
ALLENNNNNNNNNNNNNNN            ALLEN
WARDDDDDDDDD                   WARD
JONESSSSSSSSSS                 JONES
MARTINNNNNNNNNN                MARTIN
BLAKEEEE                       BLAKE
CLARKKKKKKKK                   CLARK
SCOTTTTTTTTTT                  SCOT
KINGGGG                        KING
TURNERRRRRRRR                  TURNER
ADAMSSSSSSS                    ADAMS
JAMESSSSSSSS                   JAMES
FORDDDDD                       FORD
MILLERRRRRRR                   MILLER

replacing all spaces with a single space

Laurence, February 21, 2011 - 8:45 am UTC

Simple way maybe!

v_string := REGEXP_REPLACE(v_string),'( ){2,}', ' ')

February 22, 2011 - 9:55 pm UTC

time it please.

do it a couple hundred thousand times and compare to the other way.

Like I do for you. Before suggesting something

I love regex functions - when I CANNOT DO IT OTHERWISE. Else, I don't like them them a bit.

haitham, July 12, 2013 - 10:56 pm UTC

Hi Tom
Could you please help me with problem I have
I have code translate('abcddcba','b','+' ')
I need to print like that acddca without letter b and
without space .

July 16, 2013 - 4:19 pm UTC

huh?

Regex pattern to remove duplicates.

Rajeshwaran, September 25, 2013 - 10:10 am UTC

Tom,

I have a delimited string, where I need to parse it eliminated duplicates.

given this input 01_01_01_02_03_04_02_02_05_01_06, I need the output after removing duplicates as 01_02_03_04_05_06.

I was able to remove the collocated duplicates but not the dislocated duplicates, do you have a Regex pattern to do this?

Here is what I have achieved so far, but this is incomplete since 01 is repeated in both 1st and 7th delimited values, similarly 02 is repeated in 2nd and 5th delimited values.(this pattern removes the collocated duplicates but not dislocated duplicates)

variable x varchar2(35);
exec :x := '01_01_01_02_03_04_02_02_05_01_06';
rajesh@ORA10G> select regexp_replace(:x,'([^_]+_)\1*','\1') txt1 from dual;

TXT1
-----------------------------------------------------------------------------
01_02_03_04_02_05_01_06

1 row selected.
rajesh@ORA10G>

I have a non-regex way of doing this using stragg and str2tbl, but do you have any Regex in Handy to do this?

rajesh@ORA10G> select * from table( str2tbl(:x,'_'));

COLUM
-----
01
01
01
02
03
04
02
02
05
01
06

11 rows selected.

rajesh@ORA10G> select stragg_dx(column_value) from table( str2tbl(:x,'_'));

STRAGG_DX(COLUMN_VALUE)
----------------------------------------------------------------------------
01_02_03_04_05_06

1 row selected.

rajesh@ORA10G>

Very slightly more elegant solution

Tom G, March 16, 2017 - 10:43 pm UTC

Instead of:
select
replace(
replace (
replace('&1',' ',' @'),'@ ',''),' @',' ')
from dual;

Would this be slightly more elegant, faster, and less CPU intensive (since it doesn't ever increase the length of the string):
select
replace(
replace(
replace(
'&1'
, ' ', ' ' || chr(0)),
chr(0) || ' ',''),
chr(0),'')
from dual;
?

P.S. I would test it to see if there is a difference in performance, but I don't appear to have access to tkprof.
P.P.S. I'm posting mostly so others can see this slightly-more-optimal way.

Using Code tags

Tom G, March 16, 2017 - 10:46 pm UTC

Whoops, I was having troubles with previewing/logging in on the last post. Here's it again using code tags.

Instead of:

select 
 replace( 
 replace ( 
 replace('&1',' ',' @'),'@ ',''),' @',' ') 
from dual;

Would this be slightly more elegant, faster, and less CPU intensive (since it doesn't ever increase the length of the string):

select 
  replace( 
    replace( 
      replace(
        '&1'
      , '  ', ' ' || chr(0)),
    chr(0) || ' ', ''),
  chr(0), '')
from dual;

?

P.S. I would test it to see if there is a difference in performance, but I don't appear to have access to tkprof.
P.P.S. I'm posting mostly so others can see this slightly-more-optimal way.

March 22, 2017 - 2:01 am UTC

Yes, that is a nice touch.

CPU wise I couldnt find a difference, but the length issue is important, because it avoids an error at the boundary limits, eg

SQL> select
  2   replace(
  3   replace (
  4   replace(rpad('qweqwe',3999)||'x',' ',' @'),'@ ',''),' @',' ')
  5  from dual;

REPLACE
-------
qweqwe   <==== my 'x' got lost

SQL>
SQL> select
  2    replace(
  3      replace(
  4        replace(
  5          rpad('qweqwe',3999)||'x'
  6        , '  ', ' ' || chr(0)),
  7      chr(0) || ' ', ''),
  8    chr(0), '')
  9  from dual;

REPLACE(
--------
qweqwe x

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.

Replacing Multiple Blanks by single blanks in text

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

another way

to Paul: owa_pattern is slow

Dividing a string into multiple parts

Thanks.

Thanks.

Another way of doing the same?

Dividing a string into multiple parts

replace mulit blanks in single using 3 "replace"

Dividing a String into Multiple parts

Dividing String The example is shown below

begs recursion

The three-replace solution is really nifty !

You can actually use any string instead of a single char

Another variation

Regular Expressions Rock!

Multiple ocurences of strings

the magic of primes :-)

The power of recursion?

Recursion...

why prime number

Another way

RPad and extended char's

string pattern

LIKE

RE: SQL for string pattern

String pattern

Replace

more elegant regex to use

how to replace commas with numbers

in Oracle 9i R2

in Oracle 9i R2

How this can be achieved in Oracle 10g?

Using Regular Expression in - 10G

Remove blank or others version earlier 10g

Answer for question posted above by "Krish from Bangalore,India" on May 9, 2007

replacing all spaces with a single space

Regex pattern to remove duplicates.

Very slightly more elegant solution

Using Code tags

More to Explore

Analytics