SQL query using several times the same function

Question

I have a function that converts the identification of a version to number, example, of 1.0.0 for 10000, because I can filter the results.

Function:

CREATE OR REPLACE FUNCTION f_convert_tag_to_number(ptag varchar2(12))
  RETURN NUMBER IS
  vretorno NUMBER(12);
BEGIN
  SELECT ((regexp_substr(ptag,'[^.\D]+',1,1) * 10000) +
          (regexp_substr(ptag,'[^.\D]+',1,2) * 100) +
          (regexp_substr(ptag,'[^.\D]+',1,3) * 1)) 
    INTO vretorno
    FROM dual;

  RETURN(vretorno);
END;

Consultation:

SELECT r.tag
      ,f_convert_tag_to_number(r.tag)
  FROM repositorio r
 WHERE f_convert_tag_to_number(r.tag) BETWEEN
       f_convert_tag_to_number('1.0.0') AND
       f_convert_tag_to_number('1.5.0')

Problem, this above example of query was only one example, I have a much larger query that uses this function about 15 times, and what I is to reduce the use of the function f_convert_tag_to_number

Does anyone know any way?

Thank you

Answer 1

You can create a new column to mirror the information you want:

ALTER TABLE repositorio ADD numeric_tag NUMBER(12);

And fill it through a trigger when inserting or updating:

CREATE OR REPLACE TRIGGER trigger_conversao_tag
BEFORE INSERT OR UPDATE of tag
ON repositorio
FOR EACH ROW
AS
BEGIN
  :new.numeric_tag = f_convert_tag_to_number(:new.tag);
END;

Using in the query as follows:

SELECT r.tag
      ,r.numeric_tag
  FROM repositorio r
 WHERE r.numeric_tag BETWEEN
       f_convert_tag_to_number('1.0.0') AND
       f_convert_tag_to_number('1.5.0')