----- Original Message -----

From: Svein Erling Tysværsvein.erling.tysvaer@... [firebird-support]

To: firebird-support@yahoogroups.com

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);
begin
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
begin
curpos = 1;
while (curpos <= maxlen) do
begin
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
begin
if (comparison > '') then
comparison = 'Differ more than one character';
if (comparison='') then
begin
if (ss1a = ss2b and ss1b = ss2a) then
begin
comparison = 'swapped';
curpos = curpos+1;
end
if (comparison='') then
comparison = 'One character differs';
end
end
curpos = curpos+1;
end
end
suspend;
end

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.

HTH,
Set

---

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