>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