Compare Data in Oracle

3

As the title says, I need to make a data comparison. The situation arises in the following way. I have a function that receives a parameter that parameter will be processed to remove certain characters from it.

The function is as follows:

CREATE OR REPLACE FUNCTION fReemplazaCeros(cPoliza IN VARCHAR2)
RETURN VARCHAR2
IS
    retorno VARCHAR2 (50);
    contador INTEGER (10);

BEGIN
    contador := 1;

     WHILE SUBSTR(cPoliza, contador, contador) = 0  LOOP

     contador := contador + 1;
     dbms_output.put_line('-> ' || SUBSTR(cPoliza, contador));
     retorno:= SUBSTR(cPoliza, contador);

     END LOOP;

    RETURN retorno;
END;

Basically what happens with this function is that a string is going to be checked character by character in search of finding a 0 in said chain, so that at the end of everything, all the zeros that are in this chain ( in principle) be removed.

When I do tests with this function, the following comes up:

SELECT fREEMPLAZACEROS('00000273623413') FROM DUAL;

-> 0000273623413
-> 000273623413
-> 00273623413

This function could be said to work half-heartedly, since it only finds three 0's and leaves the while cycle, the question is Is there something wrong in this function that causes the cycle to come out?

    
asked by Daniel Romero 07.12.2018 в 22:07
source

1 answer

1

I see 2 problems in your function.

  • The condition of while is SUBSTR(cPoliza, contador, contador) = 0 . If we see it step by step , with the entry '00000273623413' , I think you'll see the problem.

    • Counter is worth 1, it would be SUBSTR(cPoliza, 1, 1) , meaning '0'
    • Counter is worth 2, it would be SUBSTR(cPoliza, 2, 2) , that is '00'
    • Counter is worth 3, it would be SUBSTR(cPoliza, 3, 3) , that is to say '000'
    • Counter is worth 4, it would be SUBSTR(cPoliza, 4, 4) , that is '0027'
  • You compare a string of text with a number. This forces the motor to make a type conversion, with which performance is poor.

  • To maintain your logic, what you really want is:

    • Compare strings of a fixed length of 1 character.
    • Compare with the character '0' , to avoid type conversion.

    In code, it would be something like this:

    CREATE OR REPLACE FUNCTION fReemplazaCeros(cPoliza IN VARCHAR2)
    RETURN VARCHAR2
    IS
      retorno VARCHAR2 (50);
      contador INTEGER (10);
    
    BEGIN
      contador := 1;
    
      WHILE SUBSTR(cPoliza, contador, 1) = '0' 
      LOOP
         contador := contador + 1;
         dbms_output.put_line('-> ' || SUBSTR(cPoliza, contador));
         retorno:= SUBSTR(cPoliza, contador);
      END LOOP;
      RETURN retorno;
    END;
    
        
    answered by 07.12.2018 / 22:59
    source