How to delete more than one place in Oracle

Question

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

Here is the program I made, but that does not give me the 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

SQL> select * from asset_y;

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

after executing the procedure, I m gets the following output.

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

PL/SQL procedure successfully completed.

Answer 1

Since regexp_replace is not available in Oracle 9i, you can probably use owa_pattern procedures for a simple regular expression:

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

More about owa_pattern can be found here.

, "\ s" .

Answer 2

Oracle 9 :

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;

-:

UPDATE asset_y
SET name = replace_multi_spaces( name );

---

: Oracle 10 REGEXP_REPLACE.

Answer 3

:

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

, REC.NAME, . :

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

, . :

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

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

Answer 4

:

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 5

Ack !

T-SQL... , pl/sql, . PL/SQL , ( , substr | +)

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

, @name 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)))

, , , oracle ...

... formatname select formatname (array_y.name) ... . , patindex ('%%', array_v.name) ... , . select, , :

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...