RE: On removing spaces from string

Thankyou Jared.

-----Original Message-----
Sent: Monday, August 20, 2001 10:57 AM
To: Multiple recipients of list ORACLE-L

On Sunday 19 August 2001 21:50, Amar Kumar Padhi wrote:
> Hi,
> The need is to remove all spaces from a provided string, but if a space
> exists between two numbers, then it should not be removed. Look at the
> following examples.
>
> original string output required
> XT GB 100 XF 100 XTGB100XF100
> XT GB 123 X F 123 XTGB123XF123
> XT GB 100 100 XF XTGB100 100XF
>
> This has to be done in an sql query and not using pl/sql. Is this
possible?
>

There's no practical way to do this in SQL without using some PL/SQL.

Fortunately, most of the PL/SQL is already in the database. I've used a wrapper 'regex' below for the owa_pattern.change procedure, as the owa_pattern.change function cannot be used in a SQL statement.

The 'regex' function can then be used in a SQL statement.

Enjoy.

Jared

drop table ttest;  

create table ttest( before_data varchar2(30), after_data varchar2(30));  

insert into ttest values('XT GB 100 XF 100',  'XTGB100XF100');
insert into ttest values('XT GB 123 X F 123', 'XTGB123XF123');
insert into ttest values('XT GB 100 100 XF',  'XTGB100 100XF ');
 

commit;

create or replace function regex(

   line varchar2
   , from_str varchar2
   , to_str varchar2
) return varchar2
is

   new_line varchar2(2000);
begin

   new_line := line;
   owa_pattern.change( new_line, from_str, to_str, 'g');    return new_line;
end;
/  

show errors function regex  

select

   before_data,

• convert ~ back to space regex(
  • remove remaining spaces regex(
    • convert all \d{sp}\d to \d{~}\d regex(before_data, '([0-9]) +([0-9])', '\1~\2' ) ,'\s+','' ) ,'~',' ' ) converted_data, after_data from ttest /

~

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Amar Kumar Padhi
  INET: TS2017_at_emirates.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).