| Subject | Re: [firebird-support] Soc Sec No comparison using Firebird |
|---|---|
| Author | Softtech Support |
| Post date | 2015-06-26T11:28:18Z |
Greetings Bogdan,
Yes, SUBSTR() does exists in v1.5 so I converted it
successfully, I've alwasy used SUBSTRING() before. However, when I tried
to run a few simple comparisons with 1 character off in the strings it always
returned OK for the result. So I declared a few new variables
(sOldSSNChar, sNewSSNChar) to see what is going on while debugging and to my
surprise these varaibles would hold the first characted for both the old and new
SSN, but then the rest (2nd thru 11th position) would all be blank (empty
string), thus thinking they matched. Any idea why this may be
happening?.
Thanks,
Mike
Here is the slightly modified SP:
SET TERM ^^ ;
CREATE PROCEDURE SPS_SOCIAL_NUMBER_COMPARISON (
I1 VarChar(20),
I2 VarChar(20))
returns (
RESULT VarChar(100))
AS
declare variable x1 char(20);
CREATE PROCEDURE SPS_SOCIAL_NUMBER_COMPARISON (
I1 VarChar(20),
I2 VarChar(20))
returns (
RESULT VarChar(100))
AS
declare variable x1 char(20);
declare variable x2 char(20);
declare variable e smallint;
declare variable e1 smallint;
declare variable e2 smallint;
declare variable i smallint;
DECLARE VARIABLE sOldSSNChar Char(1);
DECLARE VARIABLE sNewSSNChar Char(1);
begin
i = 0;
x1 = i1;
x2 = i2; e = 0;
DECLARE VARIABLE sNewSSNChar Char(1);
begin
i = 0;
x1 = i1;
x2 = i2; e = 0;
while (i < 20 and e < 3)
do
begin
i = i + 1;
sOldSSNChar = substr(x1, i, 1);
sNewSSNChar = substr(x2, i, 1);
sNewSSNChar = substr(x2, i, 1);
if (substr(x1, i, 1)
<> substr(x2, i, 1)) then
begin
e = e +
1;
if (e =
1) then e1 = i;
else if
(e = 2) then e2 = i;
end
end
if (e = 0) then result = 'OK';
else if (e = 3) then result = 'Not
equal';
else if (e = 1) then result = e1 || '.
character ' || substr(i1, i, 1) || ' has changed';
else
begin
if
(substr(x1, e1,1) = substr(x2, e2, 1) and
substr(x1, e2, 1) = substr(x2, e1, 1)) then
result = e1 || ' and ' || e2 || ' were swapped';
end
suspend;
end
^^
SET TERM ; ^^
^^
SET TERM ; ^^
|
This email has been checked for viruses by Avast antivirus software.
|