Searching For Similar Strings

[TorrediPisa]

Hello to everyone!

Suppose you have a simple one column Table (TABLE_01) like this:
Mark
ABC 003
ABC003
DSA 234
FDR332
.....

I would like to create a query which returns:
ABC 003
ABC003
when I search for 'ABC003'
For Example:
SELECT some_Function_here(MARK) FROM TABLE_01 WHERE MARK = 'ABC003'

I already submitted this thread on SQL Server Forum and I've been suggested to use the implicit function "Replace" in the SQL clause, but unfortunately this function is not available in ACCESS97.

Is there exist such a function?
Best Regards
TdP

 

[PHV]

Perhaps this ?
WHERE Left(MARK,3) = 'ABC' AND Right(MARK,3) = '003'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TorrediPisa]

Thanks for yr kind reply.
I would need a more generic solution.
My aim is this:
Suppose you have a form in which you indicate a Car Licence (for example: AA12345HH) and you want to search in a database where this string could be recorded as
AA12345 HH or AA 12345 HH.
The purpose is to avoid to enter a record already existing.
Have you got an idea about?

Thank you for yr time
Regards
TdP

 

[Remou]

Perhaps:
[tt]SELECT Replace([Mark]," ","") AS Search, tblMark.Mark
FROM tblMark
WHERE (((Replace([Mark]," ",""))="AA123HH"));[/tt]

 

[Remou]

Sorry, I was thinking of something else.

 

[PHV]

Create the following function in a standard code module:

Public Function myTrimAllSpaces(theField)
If IsNull(theField) Then Exit Function
Dim i As Long, x As String, t As String
theField = Trim(theField)
For i = 1 To Len(theField)
  x = Mid(theField, i, 1)
  If x <> " " Then t = t & x
Next
myTrimAllSpaces = t
End Function

And then, in your query:
WHERE myTrimAllSpaces([MARK]) = 'AA12345HH'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Remou]

This may be of interest:

http://www.mcse.ms/archive164-2004-11-1252267.html

 

[Golom]

Or maybe

WHERE MARK LIKE 'ABC*003'

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]