Subject | Re: [firebird-support] Get records based on the third portion of a version string |
---|---|
Author | LtColRDSChauhan |
Post date | 2015-05-10T08:57Z |
Please try :
SET TERM !! ;
alter PROCEDURE SP_VERSION_NOS(
PORTION smallint,
SEPARATOR char,
EARLIER_TO integer)
RETURNS (
VERSION_NO varchar(80)
)
AS
declare variable i smallint;
declare variable SEPARATOR_POS Integer;
declare variable VERSION_PORTION varchar(80);
declare variable VERSION_PORTION_INT integer;
BEGIN
FOR SELECT
a.VERSION_NO FROM VERSION_NOS a INTO
:VERSION_NO
DO BEGIN
i = 1;
while (:PORTION > :i) do begin
select position(:SEPARATOR in :VERSION_NO) from RDB$DATABASE into :SEPARATOR_POS;
VERSION_NO = substring(:VERSION_NO from (:SEPARATOR_POS + 1) for char_length(VERSION_NO));
i = 1 + :i;
end
select position(:SEPARATOR in :VERSION_NO) from RDB$DATABASE into :SEPARATOR_POS;
i = 1 + :i;
VERSION_NO = substring(:VERSION_NO from 1 for :SEPARATOR_POS - 1);
VERSION_PORTION_INT = cast(:VERSION_NO as integer);
if(:EARLIER_TO >= :VERSION_PORTION_INT) then begin
SUSPEND;
end
END
/* SELECT p.VERSION_NO FROM SP_VERSION_NOS ('3', '.', '456') p; */
END!!
PORTION smallint,
SEPARATOR char,
EARLIER_TO integer)
RETURNS (
VERSION_NO varchar(80)
)
AS
declare variable i smallint;
declare variable SEPARATOR_POS Integer;
declare variable VERSION_PORTION varchar(80);
declare variable VERSION_PORTION_INT integer;
BEGIN
FOR SELECT
a.VERSION_NO FROM VERSION_NOS a INTO
:VERSION_NO
DO BEGIN
i = 1;
while (:PORTION > :i) do begin
select position(:SEPARATOR in :VERSION_NO) from RDB$DATABASE into :SEPARATOR_POS;
VERSION_NO = substring(:VERSION_NO from (:SEPARATOR_POS + 1) for char_length(VERSION_NO));
i = 1 + :i;
end
select position(:SEPARATOR in :VERSION_NO) from RDB$DATABASE into :SEPARATOR_POS;
i = 1 + :i;
VERSION_NO = substring(:VERSION_NO from 1 for :SEPARATOR_POS - 1);
VERSION_PORTION_INT = cast(:VERSION_NO as integer);
if(:EARLIER_TO >= :VERSION_PORTION_INT) then begin
SUSPEND;
end
END
/* SELECT p.VERSION_NO FROM SP_VERSION_NOS ('3', '.', '456') p; */
END!!
SET TERM ; !!
On Sun, May 10, 2015 at 12:08 PM, venussoftop@... [firebird-support] <firebird-support@yahoogroups.com> wrote:Hi all
I was wondering if there was a way to get a portion of a string based on a separator character. What I needed to do was want to bring out a part of the version number.
1.23.456.789
I want to get just the third portion, 456. So I could query on strings that have a version number with the third portion less than or equal to 456. Thus all records which may contain
1.1.55.0
1.15.450.688
Hope I was able to explain.
Please advise.
Thanks and regards
Bhavbhuti
--Regards,Lt Col (Retd) Rajiv D.S. Chauhan_____________________________