Link to home
Start Free TrialLog in
Avatar of dp
dpFlag for Canada

asked on

How to compare two strings and return 'true' if similar in MS Access

Is there a sql statement that will let me compare two fields in a MS Access table in such a way that I get 'true' if the two strings are similar? The two strings have to match up in the first 3 characters and also in the characters at the end of the string that come after a hyphen. E.g. 12ABC-123A is similar to 12A-123A (there can be 1 to 5 characters after the hyphen).
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Select Field1, Field2, IIF(Field1 = Field2, True, False) As DoTheFieldMatch From Table1
SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Rey Obrero (Capricorn1)
select ID, f1, f2
from tableX
where left([f1],3)=left([f2],3) and ,mid([f1],inStr([f1],"-"))=mid([f2],instr([f2],"-"))


oops

select ID, f1, f2
from tableX
where left([f1],3)=left([f2],3) and mid([f1],inStr([f1],"-"))=mid([f2],instr([f2],"-"))

Sorry, you better handle this with some function calls:

Select Field1, Field2, IIF(fnValue(Field1) = fnValue(Field2), True, False) As DoTheFieldMatch From Table1

In a module have:

Function fnValue(strVal as String) As String
Dim strPart1 As String
Dim strSecPart as string
Dim strTemp as string
Dim i as integer

strPart1 = Left(strVal ,3)
strTemp = Mid(strVal, Instr(strVal ,"-")+1)

For i = 1 to len(strTemp)
if IsNumeric(mid(strTemp,i,1)) then
else
  strSecPart  = strSecPart   & mid(strTemp,i,1)
end if
Next i

fnValue=strPart1 & strSecPart  
End Function

Mike
Avatar of dp

ASKER

Hi eghtebas,

there can be a number of characters between the first 3 characters and the hyphen in the strings that I am comparing that do not have to match for the comparison to be true. How do I tell sql to compare the characters after the hyphen (not a fixed number of characters)?

Thanks!
Dijana
Avatar of dp

ASKER

Sorry, while I wrote my comment, you posted a few more - I'll check them out first!
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dp

ASKER

Thanks guys!