2
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
You can create a column with the
tag
converted to number in the table and a Trigger that will update the column every time you make a change. Example in Mysql here in the second part of the reply– Sorack
I will analyze whether the column calculated with
trigger
takes care of all situations, if answer given the answer with thetrigger
. I wish there was another way, but thank you @Sorack.– David