How to remove more than one space in Oracle

Question

I have an Oracle table which contains data like 'Shiv------Shukla' (consider '-' as space).
Now I need to write a program which leaves just one space and removes all other spaces.

Here is the program which I've made but it is not giving me expected result.

 DECLARE
  MAX_LIMIT VARCHAR2(50):=NULL;
  REQ          VARCHAR2(20):=NULL;
 CURSOR C1 IS
  SELECT  *
  FROM ASSET_Y;
 BEGIN
  FOR REC IN C1
   LOOP
    MAX_LIMIT:=LENGTH(REC.NAME)-LENGTH(REPLACE(REC.NAME,'-'));
     FOR I IN 1..MAX_LIMIT
      LOOP
       UPDATE  ASSET_Y
       SET  NAME=REPLACE(REC.NAME,'--','-')
       WHERE  REC.SNO=ASSET_Y.SNO;
       COMMIT;
    SELECT ASSET_Y.NAME INTO REQ FROM ASSET_Y WHERE ASSET_Y.SNO=REC.SNO;    
       DBMS_OUTPUT.PUT_LINE(REQ);
      END LOOP;
   END LOOP;
 COMMIT;
 END;
/

My table is

SQL> select * from asset_y;

       SNO NAME                 FL
---------- -------------------- --
         1 Shiv------Shukla     y
         2 Jinesh               y

after running the procedure i m getting the following output.

Shiv---Shukla
Shiv---Shukla
Shiv---Shukla
Shiv---Shukla
Shiv---Shukla
Shiv---Shukla

PL/SQL procedure successfully completed.

Answer 1

With Oracle 9 you could write your own function:

CREATE FUNCTION remove_multi_spaces( in_value IN VARCHAR2 )
RETURN VARCHAR2
AS
  v_result VARCHAR2(32767);
BEGIN
  IF( in_value IS NOT NULL ) THEN
    FOR i IN 1 .. ( LENGTH(in_value) - 1 ) LOOP
      IF( SUBSTR( in_value, i, 2 ) <> '  ' ) THEN
        v_result := v_result || SUBSTR( in_value, i, 1 );
      END IF;
    END LOOP;
    v_result := v_result || SUBSTR( in_value, -1 );
  END IF;
  RETURN v_result;
END;

and call it in a single update-statement:

UPDATE asset_y
SET name = replace_multi_spaces( name );

---

BTW: With Oracle 10 you could use REGEXP_REPLACE.

Answer 2

Since regexp_replace is not available in Oracle 9i maybe you can use owa_pattern routines for simple regex replaces:

  owa_pattern.change(fStr, '\s+', ' ', 'g');

More info about owa_pattern package can be found here

Bear in mind, that "\s" will match tabs and newlines as well.

Answer 3

Another way:

CREATE OR REPLACE
FUNCTION remove_multi_spaces( in_value IN VARCHAR2 )
RETURN VARCHAR2 IS
  v_result VARCHAR2(32767) := in_value;
BEGIN
  LOOP
    EXIT WHEN INSTR(v_result,'  ') = 0;
    v_result := REPLACE(v_result, '  ', ' ');
  END LOOP;
  RETURN v_result;
END remove_multi_spaces;

Answer 4

Your problem is this part:

   SET  NAME=REPLACE(REC.NAME,'--','-')

However many times you do that within the inner loop it starts with the same value of REC.NAME as before. Changing it to this would fix it:

   SET  NAME=REPLACE(NAME,'--','-')

However, it is a pretty inefficient way to do this job if the table is large. You could instead do this:

BEGIN
   LOOP
      UPDATE ASSET_Y
      SET    NAME=REPLACE(NAME,'--','-')
      WHERE  NAME LIKE '%--%';

      EXIT WHEN SQL%ROWCOUNT = 0;
   END LOOP;
END;
/

Answer 5

Ack loops! No need to loop this

This will work in T-SQL...unfortunately I have no pl/sql environment to write this in. PL/SQL will have equivlents to everything used here (I think substr instead of substring and | instead of +)

declare @name varchar(200)
set @name = 'firstword          secondword'
select left(@name,(patindex('% %',@name)-1)) + ' '  + ltrim(substring(@name,(patindex('% %',@name)+1),len(@name)))

You'll have to retool it to work for oracle and you'll need to replace any reference to @name to asset_y.name

    select left(asset_y.name,(patindex('% %',asset_y.name)-1)) || ' ' || ltrim(substring(asset_y.name,(patindex('% %',asset_y.name)+1),len(asset_y.name)))

Sorry if it won't run as is, as I mentioned I lack an oracle install here to confirm...

Just to add...I normally turn that query above into a function named formatname and call it as select formatname(array_y.name) from... This allows me to include some form of error handling. The query will fail if patindex('% %',array_v.name) returns a null...meaning there is no space. You could do the same in a select statement using cases I guess:

select case when patindex('% %',array_v.name) > 0 then 
left(asset_y.name,(patindex('% %',asset_y.name)-1)) || ' ' || ltrim(substring(asset_y.name,(patindex('% %',asset_y.name)+1),len(asset_y.name)))
else asset_y.name
from...