Subject Re: [firebird-support] Soc Sec No comparison using Firebird
Author Softtech Support
Thanks Svein I'll dig into this to see if something like this might work.
----- Original Message -----
Sent: Wednesday, June 24, 2015 3:06 PM
Subject: Re: [firebird-support] Soc Sec No comparison using Firebird


>Greetings All,
>I would like to pass into a stored procedure two social security numbers for comparison and have the result tell me if one character has changed,
>or if two characters were switched or if it does not compare at all.
>Has anyone done anything like this they could share? Or is it even possible?

Never done anything similar before, but strictly speaking, the following would answer your question:

execute block (ss1 varchar(20) = :ss1, ss2 varchar(20) = :ss2) returns (comparison varchar(50)) as
declare variable maxlen integer;
declare variable curpos integer;
declare variable ss1a char(1);
declare variable ss1b char(1);
declare variable ss2a char(1);
declare variable ss2b char(1);
Comparison = '';
maxlen = character_length(ss1);
if (maxlen < character_length(ss2)) then
maxlen = character_length(ss2);
if (ss1 = ss2) then
comparison = 'Equal';
if (comparison = '' and (ss1 starting ss2 or ss2 starting ss1)) then
comparison = 'Substring';
if (comparison = '') then
curpos = 1;
while (curpos <= maxlen) do
ss1a = substring(ss1 from curpos for 1);
ss1b = substring(ss1 from curpos+1 for 1);
ss2a = substring(ss2 from curpos for 1);
ss2b = substring(ss2 from curpos+1 for 1);
if (ss1a is distinct from ss2a) then
if (comparison > '') then
comparison = 'Differ more than one character';
if (comparison='') then
if (ss1a = ss2b and ss1b = ss2a) then
comparison = 'swapped';
curpos = curpos+1;
if (comparison='') then
comparison = 'One character differs';
curpos = curpos+1;

I say strictly speaking, because whereas this would catch the difference between 'Hello' and 'Helol' or 'Hello' and 'Hel1o', it wouldn't catch additional or missing characters like 'Hello' vs 'Helo'. Should be fixable, but I'll leave that task for you (I'd expect you to need curpos1 and curpos2).

Having said all this, it wouldn't surprise me if there existed a UDF that did something similar to my execute block, I simply don't know since I've never needed one.


Avast logo

This email has been checked for viruses by Avast antivirus software.