Forum Stats

  • 3,784,143 Users
  • 2,254,897 Discussions
  • 7,880,709 Comments

Discussions

SQL/PL/SQL

User_BU3NG
User_BU3NG Member Posts: 102 Blue Ribbon

I have a taken following code from Community,


SET SERVEROUTPUT ON;

DECLARE

 v_Str VARCHAR2(100);

 V_STR2 VARCHAR2(100);

 result1 VARCHAR2(100);

BEGIN

 v_Str := '600,1001,500,200,300,400' ;

 v_Str2 :='100,200' ;

 with testdata as(

select v_Str v_test1, v_Str2 v_test2 from dual )

select

  trim(',' from

       regexp_replace(

           regexp_replace(','||replace(v_test1,',',',,')||','

                         ,','||replace('('||v_test2||')',',',')|(')||','

           )

       ,',{2,}',','

       )

   ) result into result1

from testdata ;

DBMS_OUTPUT.PUT_LINE ( 'Result set values are: '||result1 );

END;


Result Set coming in following way:

PL/SQL procedure successfully completed.

Result set values are: 600,1,500,300,400

I need in such a way : 1001 is not in v_str2 but its getting partial string replace, I want only if the string exact match then it should find/ replace or it should ignore. Can any one please help?


Thanks

Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,433 Red Diamond
    Accepted Answer

    Hi,

    If you want to do this in PL/SQL, then it will be simpler and more efficient not to use regular expressions. Here's one way to do it:

    DECLARE
      v_test1  VARCHAR2 (100) := '600,1001,500,200,300,400' ;
      v_test2  VARCHAR2 (100) := ',100,200,' ;
      result1  VARCHAR2 (100) := ',' || v_test1 || ',';
      pre_pos  PLS_INTEGER   := 1;
      post_pos  PLS_INTEGER;
    BEGIN
      WHILE pre_pos > 0
      LOOP
           post_pos := INSTR ( v_test2
    		   	 , ','
    			 , pre_pos + 1
    			 );
    	result1 := REPLACE ( result1
    		  	   , SUBSTR ( v_test2
    			   	    , pre_pos
    				    , post_pos + 1 - pre_pos
    				    )
    			   , ','
    			   );
    	pre_pos := post_pos;
      END LOOP;
    
      result1 := TRIM (',' FROM result1);
      dbms_output.put_line ('Result set values are: ' || result1);
    END;
    /
    

    If you prefer, you can use more local variables (e.g., for the results of SUBSTR). It wn't be significantly slower, if at all.

    The dual table is very handy in SQL, because it can simulate the := operator and loops. PL/SQL has the := operator and loops, so the dual table isn't that useful in PL/SQL.

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,433 Red Diamond

    Hi, @User_BU3NG

    Do you want to use SQL or PL/SQL?

    Either way, the second argument of the inner REGEXP_REPLACE should be something like this:

     (,x,)|(,y,)|(,z,)

    with a comma after each left '(' and another comma before each right ')'. One way to do that in SQL is:

    WITH  testdata (v_test1, v_test2)  AS 
    (
    	SELECT '600,1001,500,200,300,400', '100,200' FROM dual
    )
    SELECT TRIM ( ','
       	    FROM REGEXP_REPLACE ( REGEXP_REPLACE ( ',' || REPLACE (v_test1, ',', ',,') || ','
                 	                , '(,' || REPLACE ( v_test2
    			    	 	 	  , ','
    					 	  , ',)|(,'
    						  )
    					|| ',)'
          		    )
        			 	, ',{2,}'
    				, ','
        			 	)
               ) AS result
    FROM  testdata;
    


  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,433 Red Diamond
    Accepted Answer

    Hi,

    If you want to do this in PL/SQL, then it will be simpler and more efficient not to use regular expressions. Here's one way to do it:

    DECLARE
      v_test1  VARCHAR2 (100) := '600,1001,500,200,300,400' ;
      v_test2  VARCHAR2 (100) := ',100,200,' ;
      result1  VARCHAR2 (100) := ',' || v_test1 || ',';
      pre_pos  PLS_INTEGER   := 1;
      post_pos  PLS_INTEGER;
    BEGIN
      WHILE pre_pos > 0
      LOOP
           post_pos := INSTR ( v_test2
    		   	 , ','
    			 , pre_pos + 1
    			 );
    	result1 := REPLACE ( result1
    		  	   , SUBSTR ( v_test2
    			   	    , pre_pos
    				    , post_pos + 1 - pre_pos
    				    )
    			   , ','
    			   );
    	pre_pos := post_pos;
      END LOOP;
    
      result1 := TRIM (',' FROM result1);
      dbms_output.put_line ('Result set values are: ' || result1);
    END;
    /
    

    If you prefer, you can use more local variables (e.g., for the results of SUBSTR). It wn't be significantly slower, if at all.

    The dual table is very handy in SQL, because it can simulate the := operator and loops. PL/SQL has the := operator and loops, so the dual table isn't that useful in PL/SQL.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,433 Red Diamond

    Hi,

    You can also do the job in SQL without using regular expressions. For example:

    WITH  r (result0, bad_id_list, comma_pos)  AS
    (
      SELECT  ',' || v_test1 || ','
      ,	  v_test2 || ','
      ,	  INSTR ( v_test2 || ','
      	  	 , ','
    		 )
      FROM    testdata
    UNION ALL
      SELECT REPLACE ( result0
      	  	  , ',' || SUBSTR ( bad_id_list
    		   	   	  , 1
    				  , comma_pos
    				  )
    		  , ','
    		  )
      ,       SUBSTR (bad_id_list, comma_pos + 1)
      , 	  INSTR ( SUBSTR (bad_id_list, comma_pos + 1)
      	  	 , ','
    		 )
      FROM    r
      WHERE   bad_id_list IS NOT NULL
    )
    SELECT  TRIM (',' FROM result0) AS result1
    FROM	 r
    WHERE	 bad_id_list IS NULL
    ;