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);
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;
while (i < 20 and e < 3) do
begin
i = i + 1;
sOldSSNChar = substr(x1, 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 ; ^^
---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus
